Question 90119 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
;
    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search