Re: Totalling Counts done in Subqueries

[Peter Brawley]

> Is there a way to total counts done in subqueries?
Select expression aliases can't be referenced at the same level. You 
have to create another outer level ...

SELECT
 note_id,  last_updated_datetime,event_date,subject,summary,content,
 linked_issues_count,linked_people_count,  linked_organisations_count,
 linked_issues_count + linked_people_count + linked_organisations_count 
AS total
FROM (
 SELECT
   note_id,last_updated_datetime,event_date,subject,summary,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',
 FROM Notes
 WHERE added_user_id = 6
 ORDER BY last_updated_datetime DESC
 LIMIT 25
) AS tmp;

PB
http://www.artfulsoftware.com

-----

Nigel Peck wrote:

Hi all,

I'm hoping someone can help me with this please.

Is there a way to total counts done in subqueries?

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

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

Note the:

(linked_issues_count + linked_people_count + 
linked_organisations_count) AS 'total'

That's the bit that doesn't work. Is there a way?

Thanks
Nigel

------------------------------------------------------------------------


No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.0.238 / Virus Database: 270.12.8/2086 - Release Date: 04/29/09 06:37:00