From: Nigel Peck
Date: April 21 2009 7:56pm
Subject: Two "COUNT"s in one query
List-Archive: http://lists.mysql.com/mysql/217215
Message-Id: <49EE24F0.2090906@miswebdesign.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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