List:General Discussion« Previous MessageNext Message »
From:Nigel Peck Date:April 21 2009 7:56pm
Subject:Two "COUNT"s in one query
View as plain text  
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
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