List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 21 2009 8:44pm
Subject:Re: Two "COUNT"s in one query
View as plain text  
Nigel,

 >I want to select the name and id from "Organisations"
 >along with a count of the number of one-to-many
 >relationships it has in each of the two tables.

Aggregation multiplies across multiple joins. For suggested solutions 
see "Aggregates across multiple joins" at 
http://www.artfulsoftware.com/infotree/queries.php.

PB

-----

Nigel Peck wrote:
>
> I think I probably can't do what I want, but am hoping I'm wrong. 
> Please help :)
>
> I have three tables:
>
> Organisations
> ----------------------------
> - organisation_id - name   -
> ----------------------------
> - 1               - Org A  -
> - 2               - Org B  -
> ----------------------------
>
> Notes__Organisations
> -----------------------------
> - organisation_id - note_id -
> -----------------------------
> - 1               - 10      -
> - 1               - 11      -
> -----------------------------
>
> Organisations__People
> -------------------------------
> - organisation_id - person_id -
> -------------------------------
> - 1               - 20        -
> - 1               - 21        -
> - 1               - 22        -
> - 1               - 23        -
> - 2               - 24        -
> -------------------------------
>
> "Organisations" has one-to-many relationships with 
> "Notes__Organisations" and "Organisations__People".
>
> I want to select the name and id from "Organisations" along with a 
> count of the number of one-to-many relationships it has in each of the 
> two tables. I want a count of 0 if there are none, so I'm using two 
> LEFT JOINs and a GROUP BY.
>
> I want to do this in a single query (so I can limit the set and sort 
> it, displaying the limited set in my interface).
>
> I have:
>
> =========================================
> SELECT
>
> `Organisations`.`organisation_id`,
> `Organisations`.`name`,
> COUNT(`Notes__Organisations`.`organisation_id`) AS 'linked_notes_count',
> COUNT(`Organisations__People`.`organisation_id`) AS 'linked_people_count'
>
> FROM
> `Organisations`
>
> LEFT JOIN
> `Notes__Organisations`
> ON
> `Organisations`.`organisation_id` = 
> `Notes__Organisations`.`organisation_id`
>
> LEFT JOIN
> `Organisations__People`
> ON
> `Organisations`.`organisation_id` = 
> `Organisations__People`.`organisation_id`
>
> GROUP BY
> `Organisations`.`organisation_id`
>
> ORDER BY
> name
>
> LIMIT 50, 25
> =========================================
>
> Obviously the LIMIT does not apply to this example data, but I wanted 
> to show my whole query.
>
> So with the above data I want:
>
> -----------------------------------------------------------------------
> - organisation_id - name   - linked_notes_count - linked_people_count -
> -----------------------------------------------------------------------
> - 1               - Org A  - 2                  - 4                   -
> - 2               - Org B  - 0                  - 1                   -
> -----------------------------------------------------------------------
>
> What I actually get is unpredictable. Something like:
>
> -----------------------------------------------------------------------
> - organisation_id - name   - linked_notes_count - linked_people_count -
> -----------------------------------------------------------------------
> - 1               - Org A  - 4                  - 4                   -
> - 2               - Org B  - 0                  - 1                   -
> -----------------------------------------------------------------------
>
> But it varies and there's no pattern.
>
> Is there a way? Should I use a stored procedure instead to do the 
> counts? (not used them yet - don't even know if that's a valid 
> suggestion).
>
> Thanks in advance,
> Nigel
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.0.238 / Virus Database: 270.12.1/2071 - Release Date: 04/21/09 08:30: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