List:General Discussion« Previous MessageNext Message »
From:Nigel Peck Date:April 23 2009 4:03pm
Subject:Re: Two "COUNT"s in one query
View as plain text  
Thanks to everyone who helped me out with this, just what I needed and 
this is now working for me.

One further question...

I'm using a subquery as suggested:

SELECT

  `Organisations`.`organisation_id`,
  `Organisations`.`name`,
  (SELECT COUNT(*) FROM `Notes__Organisations` WHERE
`Notes__Organisations`.`organisation_id` =
`Organisations`.`organisation_id`) AS 'linked_notes_count',
  (SELECT COUNT(*) FROM `Organisations__People` WHERE
`Organisations__People`.`organisation_id` =
`Organisations`.`organisation_id`) AS 'linked_people_count'

  FROM
  `Organisations`

Is there a way to total the counts?

So I want to do:

-=-=-=-=-=-=-=

SELECT
`Notes`.`note_id`,
`Notes`.`last_updated_datetime`,
`Notes`.`event_date`,
`Notes`.`subject`,
`Notes`.`summary`,
`Notes`.`content`,

(SELECT COUNT(*) FROM `Notes__Issues` WHERE `Notes`.`note_id` = 
`Notes__Issues`.`note_id` )
AS 'linked_issues_count',
	
(SELECT COUNT(*) FROM `Notes__People` WHERE `Notes`.`note_id` = 
`Notes__People`.`note_id` )
AS 'linked_people_count',
	
(SELECT COUNT(*) FROM `Notes__Organisations` WHERE `Notes`.`note_id` = 
`Notes__Organisations`.`note_id` )
AS 'linked_organisations_count',
	
(linked_issues_count + linked_people_count + linked_organisations_count)
AS 'total'

FROM `Notes`
WHERE ( `added_user_id` = '6' )
ORDER BY last_updated_datetime DESC
LIMIT 25

-=-=-=-=-=-=-=-=-

Notes the "(linked_issues_count + linked_people_count + 
linked_organisations_count) AS 'total'".

The above does not work, is there a way?

Thanks
Nigel
Thread
Sun bought by OracleAndy Shellam20 Apr
  • Re: Sun bought by OracleKrishna Chandra Prajapati20 Apr
    • Re: Sun bought by OracleAndy Smith20 Apr
      • Re: Sun bought by Oraclemos20 Apr
        • I thin'k MySQL will be the 'Oracle Personal Edition'José I .  Merino22 Apr
          • Re: I thin'k MySQL will be the 'Oracle Personal Edition'Thomas Pundt22 Apr
            • Re: I thin'k MySQL will be the 'Oracle Personal Edition'Arthur Fuller22 Apr
            • Re: I thin'k MySQL will be the 'Oracle Personal Edition'Lin Chun22 Apr
    • Re: Sun bought by Oraclemos20 Apr
  • Re: Sun bought by OracleCurtis Maurand20 Apr
    • Re: Sun bought by OracleArthur Fuller20 Apr
      • Re: Sun bought by OraclePeter Brawley20 Apr
  • Re: Sun bought by Oraclerussbucket21 Apr
    • Re: Sun bought by OracleArthur Fuller21 Apr
      • RE: Sun bought by OracleJoshua Gordon21 Apr
        • Two "COUNT"s in one queryNigel Peck21 Apr
          • Re: Two "COUNT"s in one queryPeter Brawley21 Apr
            • Re: Two "COUNT"s in one queryNigel Peck23 Apr
              • Totalling Counts done in SubqueriesNigel Peck30 Apr
                • Re: Totalling Counts done in SubqueriesPeter Brawley30 Apr
                  • Re: Totalling Counts done in Subqueriesnigel wood30 Apr
RE: Sun bought by OracleMichael.Coll-Barth20 Apr
Re: Sun bought by OracleEric Bergen20 Apr
  • Re: Sun bought by OracleJohn Meyer20 Apr
    • Re: Sun bought by OracleKaushal Shriyan20 Apr
      • Re: Sun bought by OracleJohn Meyer20 Apr
        • Re: Sun bought by OracleManish Gupta21 Apr
          • Re: Sun bought by OracleMoon's Father21 Apr
  • Re: Sun bought by OracleClaudio Nanni20 Apr
    • Re: Sun bought by OracleMichael Backhaus20 Apr
Re: Totalling Counts done in SubqueriesNigel Peck30 Apr