Why is my SQL statement counting different fields from different tables in the same SQL statement?

Counter () displays the number of rows found for your group. Since you are grouping properties, it will count the number of rows you attach to properties. The joins will multiply the number of lines, so 3 x 10 = 30 will look like the correct count. Run the query without GROUP BY to see what's going on.

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 

      

        
        
        
      

    

Quoting the informational response from Alex Martelli:

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 to the 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)