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


  

Re: Totalling Counts done in Subqueries

[nigel wood]

Peter Brawley wrote:

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



alternatively use variables:

mysql select @first := 1 as value1, @second := 2 as value2, 
@fir...@second as total;

+++---+
| value1 | value2 | total |
+++---+
|  1 |  2 | 3 |
+++---+
1 row in set (0.03 sec)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Totalling Counts done in Subqueries

[Nigel Peck]

Thanks Nigel and Peter, I went for Nigel's solution below. Both very 
useful, learnt a lot, thank you.


Cheers,
Nigel

nigel wood wrote:

  Is there a way to total counts done in subqueries?

Never done this but my educated guess is:

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

@linked_issues_count := (SELECT COUNT(*) FROM `Notes__Issues` WHERE 
`Notes`.`note_id` = `Notes__Issues`.`note_id` )

AS 'linked_issues_count',

@linked_people_count := (SELECT COUNT(*) FROM `Notes__People` WHERE 
`Notes`.`note_id` = `Notes__People`.`note_id` )

AS 'linked_people_count',

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

hope that helps

Nigel




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org