List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 30 2009 1:33pm
Subject:Re: Totalling Counts done in Subqueries
View as plain text  
 > 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
>
>   

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