From: Nigel Peck
Date: April 23 2009 4:03pm
Subject: Re: Two "COUNT"s in one query
List-Archive: http://lists.mysql.com/mysql/217250
Message-Id: <49F09138.7090109@miswebdesign.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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