I have the following table:

CREATE TABLE table1
    ("id" int, "day" varchar(8), "customer" varchar(1), "amount" int)
;


INSERT INTO Table1
    ("id", "day", "customer", "amount")
VALUES
    (1, 'Thursday', 'a', 10),
    (2, 'Thursday', 'a', 11),
    (3, 'Thursday', 'b', 12),
    (4, 'Friday', 'c', 13),
    (5, 'Friday', 'c', 14),
    (6, 'Saturday', 'c', 15),
    (7, 'Saturday', 'a', 16),
    (8, 'Saturday', 'c', 17),
    (9, 'Saturday', 'c', 18),
    (10, 'Saturday', 'a', 19)
;

And I need to have the average amount for each day-customer combination (3x3=9 rows). However, when I use this following simple query, days where a particular customer did not spent money are not shown. I would like them to be included and show an average of zero.

Query:

Select day, customer, avg(amount) 
FROM table1
GROUP BY day, customer 

Result:

+-----------+-----------+---------------------+
|   day     | customer  |         avg         |
+-----------+-----------+---------------------+
| Friday    | c         | 13.5000000000000000 |
| Saturday  | c         | 16.6666666666666667 |
| Thursday  | b         | 12.0000000000000000 |
| Thursday  | a         | 10.5000000000000000 |
| Saturday  | a         | 17.5000000000000000 |
+-----------+-----------+---------------------+

Desired result:

+----------+----------+------------------+
|   day    | customer |       avg        |
+----------+----------+------------------+
| Friday   | a        | 0                |
| Friday   | b        | 0                |
| Friday   | c        | 13.5             |
| Saturday | a        | 17.5             |
| Saturday | b        | 0                |
| Saturday | c        | 16.6666666666667 |
| Thursday | a        | 10.5             |
| Thursday | b        | 12               |
| Thursday | c        | 0                |
+----------+----------+------------------+

Fiddle: [https://www.db-fiddle.com/f/gT5FoFbvC951CHdFXBsfYH/0][1] Any help is much appreciated.

1 Answers

1
Gordon Linoff On Best Solutions

Use a cross join to generate the rows and a left join to bring in the values:

select d.day, c.customer, coalesce(avg(t.amount), 0)
from (select distinct day from table1) d cross join
     (select distinct customer from table1) c left join
     table1 t
     on t.day = d.day and t.customer = c.customer
group by d.day, c.customer;

If you have other sources for the customers or days, you can use them instead of the subqueries.