Left join with count group by not displaying all left tables null records-CodePudding

I have two tables Broadcastlists and Contacts(foreign key of broadcastlist). I want to show broadcastlist tables all records and count of broadcastlistid in contacts table.

My Query:-

SELECT b.id, count(c.broadcastlist_id)as Recepients,b.name 
from Broadcastlists b 
LEFT JOIN Contacts c ON b.id = c.broadcastlist_id 
group by c.broadcastlist_id;

Broadcastlists:

Id	Name
1	Test 1
2	Test 2
3	Test 4
4	Test 5

Contacts:

Id	Name	Broadcastlist_id
1	Rahul	2
2	Mansi	1
3	Nisha	2
4	Nidhi	2
5	Prashant	1

I want Output like this

Id	Name	Recepients(count)
1	Test 1	2
2	Test 2	3
3	Test 3	0
4	Test 4	0

But, Output come like this, shows only one null record from left table I want all null data from left table

Id	Name	Recepients(count)
1	Test 1	2
2	Test 2	3
3	Test 3	0

CodePudding user response：

You have grouped with a wrong column. Try this:

SELECT b.id, b.name, COUNT(c.broadcastlist_id) AS Recepients 
FROM Broadcastlists b 
LEFT JOIN Contacts c ON b.id = c.broadcastlist_id 
GROUP BY b.id, b.name;

Output

id	name	Recepients
1	Test 1	2
2	Test 2	3
3	Test 4	0
4	Test 5	0

See this db<>fiddle.