Why are my SQL statement count different fields from differrent tables in one SQL statement?

Question

I have a SQL query:

SELECT 
    e.name as estate_name 
    , g.name as governing_body
    , count(s.id) as total_stands 
    , count(sp.id) as service_providers 
FROM estates e 
    LEFT JOIN governing_bodies    
        ON e.governing_body_id = g.id 
    LEFT JOIN stands s 
        ON s.estate_id = e.id 
    LEFT JOIN services sp 
        ON sp.estate_id = e.id 
GROUP BY e.id

It seems like my counts multiply each other. If my first count is 3 and second count is 10 the results in service_providers field and total_stands field will be 30.

What am I doing wrong?

Answer 1

A count() displays the number of rows found for your group. Since you're grouping on estate, it will count the number of rows you join to estate. Joins will multiply the number of rows, so 3 x 10 = 30 sounds like the correct count. Run the query without GROUP BY to see what's happening.

One way to fix it would look like this:

SELECT 
    e.name as estate_name, 
    g.name as governing_body, 
    (select count(*) from stands s where s.estate_id = e.id) as stands,
    (select count(*) from services sp where sp.estate_id = e.id) as services
FROM estates e
LEFT JOIN governing_bodies g on e.governing_body_id = g.id

Writing out Alex Martelli's informative answer:

SELECT 
    e.name as estate_name 
    , g.name as governing_body
    , count(distinct s.id) as total_stands 
    , count(distinct sp.id) as service_providers 
FROM estates e 
    LEFT JOIN governing_bodies    
        ON e.governing_body_id = g.id 
    LEFT JOIN stands s 
        ON s.estate_id = e.id 
    LEFT JOIN services sp 
        ON sp.estate_id = e.id 
GROUP BY e.id, g.name

Or, as a more complex alternative with JOIN syntax:

SELECT 
    e.name as estate_name, 
    g.name as governing_body, 
    IsNull(stand_count.total,0) as stand_count,
    IsNull(service_count.total,0) as service_count
FROM estates e
LEFT JOIN governing_bodies g on e.governing_body_id = g.id
LEFT JOIN (
   select estate_id, total = count(*) from stands group by estate_id
) stand_count on stand_count.estate_id = e.id
LEFT JOIN (
    select estate_id, total = count(*) from services group by estate_id
) service_count on service_count.estate_id = e.id
GROUP BY
    e.name,
    g.name,
    IsNull(stand_count.total,0),
    IsNull(service_count.total,0)

Answer 2

What about changing the COUNT(blah) constructs to COUNT(DISTINCT blah) ?