Get Count 0 if there are no entries in the RIGHT Table

Websites:

website_Id	website_name
1	website_a
2	website_b
3	website_c
4	website_d
5	website_e

Fixtures:

fixture_Id	website_id	fixture_details
1	1	a vs b
2	1	c vs d
3	2	e vs f
4	2	g vs h
5	4	i vs j

Expected Output:

website_Id	website_name	TotalRows
1	website_a	2
2	website_b	2
3	website_c	0
4	website_d	1
5	website_e	0

I would like to get 0 when there are no entries in the fixture table.

Select fx.website_id, ws.website_name, Count (*) as TotalRows 
FROM fixtures fx
LEFT JOIN websites ws on ws.website_id = fx.website_id
WHERE date_of_entry = '16-01-2023'
GROUP BY
  fx.website_id, ws.website_name

But this does not return 0 when there are no entries.

How can I change my SQL to reflect this?

Solution

You are very close, the reason why you cannot get those records with 0 count is because if there are no related fixture records for the specific website, date_of_entry will be NULL which WHERE date_of_entry = '16-01-2023' will filter all those records out. So the solutions are either put it in the LEFT JOIN condition or add an extra condition in where clause. Another core problem is you are grouping count by website related data, you MUST select from website or RIGHT JOIN to fixtures to keep all website records showing in result.

Solution A

Select ws.id AS website_id, ws.website_name, Count (fx.*) as TotalRows 
FROM websites ws 
LEFT JOIN fixtures fx on ws.website_id = fx.website_id AND date_of_entry = '16-01-2023'
GROUP BY
  ws.id, ws.website_name
;

Solution B

Select ws.id AS website_id, ws.website_name, Count (fx.*) as TotalRows 
FROM websites ws 
LEFT JOIN fixtures fx on ws.website_id = fx.website_id
WHERE date_of_entry IS NULL OR date_of_entry = '16-01-2023'
GROUP BY
  ws.id, ws.website_name
;