CODEWITHSUNDEEP
sql-serverjoingroup-bycount
Rudeman
Rudeman

Reputation: 5

Count missing values on SQL join

Can you please advise on a proper query to count including categories with no data?

Table1

|ID | NAME
+---+------
| 1 | AAA
| 2 | BBB
| 3 | CCC
| 4 | DDD

Table2

GroupName | GUID | ID
----------+------+-----
 GroupA   | zxcv |  1
 GroupA   | zxdt |  1
 GroupA   | fhgg |  2
 GroupA   | fhjg |  2
 GroupA   | fqwe |  2
 GroupB   | ffgh |  3
 GroupB   | yjkl |  3
 GroupB   | poiu |  2
 GroupB   | mnju |  2
 GroupB   | gytd |  2

I'm trying to do 

SELECT 
    B.GroupName,
    A.NAME,
    COUNT(DISTINCT B.GUID) Quantity
FROM
    Table2 B
LEFT JOIN 
    Table1 A ON B.ID = A.ID
GROUP BY 
    B.GroupName, A.NAME

As result I'm getting

GroupA | AAA | 2
GroupA | BBB | 3
GroupB | CCC | 2
GroupB | BBB | 3

But I want to see

GroupA | AAA | 2
GroupA | BBB | 3
GroupA | CCC | 0
GroupA | DDD | 0
GroupB | AAA | 0
GroupB | BBB | 3
GroupB | CCC | 2
GroupB | DDD | 0

Any help appreciated...

Upvotes: 0

Views: 125

Answers (1)

forpas
forpas

Reputation: 164174

You must cross join Table1 with all the distinct groupnames of Table2 and then left join to Table2 to aggregate:

select d2.groupname, t1.name, count(t2.id) counter
from table1 t1 cross join (select distinct groupname from table2) d2
left join table2 t2
on t2.groupname = d2.groupname and t2.id = t1.id
group by d2.groupname, t1.name
order by d2.groupname, t1.name

See the demo.
Results

> groupname | name | counter
> :-------- | :--- | ---------------:
> GroupA    | AAA  |                2
> GroupA    | BBB  |                3
> GroupA    | CCC  |                0
> GroupA    | DDD  |                0
> GroupB    | AAA  |                0
> GroupB    | BBB  |                3
> GroupB    | CCC  |                2
> GroupB    | DDD  |                0

Upvotes: 1

Related Questions