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

Question

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?

What table is the `date_of_entry` column in? If it's in your websites table then this `where` clause will filter out any records that don't have records in the `websites` table. To avoid that issue, replace `where` with `and` so that this condition becomes a part of your `join` statement instead; thus it won't impact the results from the `fixtures` table. Side note: you may want to consider replacing `ws.website_name` with `coalesce(ws.website_name,'[not found]')` in your `select` and `group by` clauses so that you don't have `null`s in your output. — JohnLBevan, Jan 16 '23 at 16:27
Debug questions require a [mre]. [ask] [Help] PS LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You may have that. PS This is a faq. But one must pin down via a [mre] & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably. — philipxy, Jan 16 '23 at 19:24
[Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097) [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) [Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?](https://stackoverflow.com/a/55111083/3404097) etc etc — philipxy, Jan 16 '23 at 19:27

score 1 · Answer 1 · answered Jan 16 '23 at 16:21

1

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.

answered Jan 16 '23 at 16:21

Islingre

2,030
6
18

I tried your query and it returned the same results as I am returning, ie no zeros when the website_id does not exist in the fixtures table. Date_of_entry is another field I did not list it but basically its a date for example 16-01-2023 – JMon Jan 16 '23 at 16:51
Yeah, so your additional ```WHERE``` clause is more relevant than you thought at first. It kicks out the rows that have ```NULL``` as ```date_of_entry``` (which are indeed all rows where no fixture is joined; if there is no fixture to join, the ```LEFT/RIGHT JOIN``` will instead join it with a record consisting of nulls only. So if you apply the condition only to fixtures and not to the whole join result, it should work. – Islingre Jan 18 '23 at 14:50

tom10271 · Accepted Answer · 2023-01-16T18:19:10.863

1

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
;

edited Jan 16 '23 at 18:19

answered Jan 16 '23 at 16:32

tom10271

4,222
5
33
62

Hi tom, solution A is returning all the records not just the date_of_entry, and solution B is returning the same exact recordset as I am returining, no zeros – JMon Jan 16 '23 at 17:01
@JMon Please try the latest update. I fixed the SQL – tom10271 Jan 16 '23 at 17:05
Hi tom, Solution A is almost working, however I am getting a total of 1 instead of 0 for those that do not exist. Any idea why? – JMon Jan 16 '23 at 17:21
It is hard to tell when I don't have access to data. Can you list the sample data and which website shows 1 instead of 0? @JMon – tom10271 Jan 16 '23 at 18:13
@JMon I know the reason why. Updated the SQL. I got to sleep, 02:00 AM in my timezone. – tom10271 Jan 16 '23 at 18:19
Perfect thanks tom! It works perfectly now! Sorry to keep you awake mate – JMon Jan 16 '23 at 18:38

JohnLBevan · Answer 3 · 2023-01-16T18:28:15.270

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

John I tried your solution but i am not getting any zeros, only the resultset I was getting back myself. — JMon, Jan 16 '23 at 18:04
Apologies- I'd made a wrong assumption abou what you were trying to do / hadn't looked closely enough! Try the corrected version. — JohnLBevan, Jan 16 '23 at 18:28

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

3 Answers3

CORRECTION