Odtnhj

How to select rows which are not defined? Like row 2 have undefined day 3 and row 3 have undefined day 1. I want them to be 0 in result set.

+----+-----+-------+
| id | day | count |
+----+-----+-------+
| 1 | 1 | 262 |
| 1 | 2 | 685 |
| 1 | 3 | 984 |
| 2 | 1 | 692 |
| 2 | 2 | 962 |
| 3 | 2 | 355 |
| 3 | 3 | 741 |
+----+-----+-------+

EDIT:
I want select count from days 1, 2 and 3 (not whole table) and display 0 on undefined day.

• We can get all unique id values in a Derived Table.


• For day, you seem to want only 1,2 and 3 only. So we can directly consider these values only using UNION ALL.


• 
  CROSS JOIN between them to get all possible combinations.


• 
  LEFT JOIN from all_combinations table to the main table on id and day.


• We can use Coalesce() function to consider 0 value for count, for the cases where there is no matching row in the main table

Try the following:

SELECT all_combinations.id, 
 all_combinations.day, 
 COALESCE(t.count, 0) AS count 
FROM 
(
 SELECT ids.id, days.day 
 FROM
 (SELECT DISTINCT id FROM your_table) AS ids 
 CROSS JOIN 
 (SELECT 1 AS day UNION ALL SELECT 2 UNION ALL SELECT 3) AS days
) AS all_combinations 
LEFT JOIN your_table AS t
 ON t.id = all_combinations.id AND 
 t.day = all_combinations.day

Result:

| id | day | count |
| --- | --- | ----- |
| 1 | 1 | 262 |
| 2 | 1 | 692 |
| 3 | 1 | 0 |
| 1 | 2 | 685 |
| 2 | 2 | 962 |
| 3 | 2 | 355 |
| 1 | 3 | 984 |
| 2 | 3 | 0 |
| 3 | 3 | 741 |

View on DB Fiddle