> 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
>
>
