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