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?

CodePudding user response：

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
;

CodePudding user response：

Try the following statement:

SELECT ws.website_id, ws.website_name, COUNT(fx.id) AS number_of_fixtures
FROM websites ws
LEFT JOIN fixtures fx ON fx.website_id = ws.website_id
WHERE TRUE -- or whatever condition you want but I do not know where to take date_of_entry from
GROUP BY ws.website_id

COUNT with an expression as argument evaluates for each row this expression and does not count the row if it evaluates to NULL.

If you want to stick to your order of joins, you would need fixtures RIGHT JOIN websites.

CodePudding user response：

The issue is that you're counting *; i.e. the number of rows regardless of table; so you'll be getting 1 when there's only a record from the fixtures table as you've returned 1 row. You can get around this by counting rows from the websites table by using count(ws.website_id) instead; since where there are results from this table, this field would return a non-null value and thus be counted; whilst where there's no record this field would be null, and thus not counted.

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

---

CORRECTION

Apologies - I'd not looked closely enough / had been returning all fixtures with just websites where those exist... Please try this: DB Fiddle

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