Group by with unrelated counts I have three tables, one, two and three.

one



two



three



With this query

select one.id, count(two.id) as num_twos, count(three.id) as num_threes from one
left join two on one.id = two.one_id
left join three on one.id = three.one_id
group by one.id


I expect to get

1 2 1
2 1 4
3 0 0
4 0 0


because there are


two records in two that relates to one_id 1 and one record in three that relates to one_id 1.
one record in two that relates to one_id 2 and four records in three that relates to one_id 2.


but I get



Why? The counts seems to get multiplied. Can I get the desired behavior without subqueries?
Image this :

select one_id,count(id) as num_Twos
from Two
group by one_id


with the output : 

one_id      num_Twos
1           2
2           1


And then join to the main table (this way, the main records, from table One, are not duplicated,and no need of distinct ):

select
    id, num_Twos, isnull(num_Twos,0) as numTwos
from One 
    left join
    (
        select one_id,count(id) as num_Twos
        from Two
        group by one_id
    )as two
        ON id =two.one_id


output: (used isnull - to put 0 instead of NULL)

id          num_Twos    numTwos
1           2           2
2           1           1
3           NULL        0
4           NULL        0


the same thing , with table 3
From comments: this way, you don't need the top level group by , because the all the information are already aggregated in the () as Two and () as Three

or with distinct:

select one.id
        , count(distinct two.id) as num_twos
        , count(distinct three.id) as num_threes 
from one
left join two on one.id = two.one_id
left join three on one.id = three.one_id
group by one.id


output :

id          num_twos    num_threes
1           2           1
2           1           4
3           0           0
4           0           0


the source:

with One 
as (select 1 id union all select 2 union all select 3 union all select 4)
,Two 
as (select 1 as id, 1 as one_id Union all select 2,1 union all select 3,2)
,Three
as (select 1 as id,1 as one_id union all select 2,2 union all select 3,2 union all select 4,2 union all select 5,2)


also , you can rewrite it as :

select id
        , isnull(num_twos,0) as num_twos
        , isnull(num_threes,0) as num_threes
from one
left join (select one_id ,count(id) as num_twos from two group by one_id) as two on one.id = two.one_id
left join (select one_id ,count(id) as num_threes from three group by one_id) as three on one.id = three.one_id