How do I select SQL data in buckets when data doesn't exist for one bucket?

Question

I'm trying to get a complete set of buckets for a given dataset, even if no records exist for some buckets.

For example, I want to display totals by day of week, with zero total for days with no records.

SELECT
WEEKDAY(transaction_date) AS day_of_week,
SUM(sales) AS total_sales
FROM table1
GROUP BY day_of_week

If I have sales every day, I'll get 7 rows in my result representing total sales on days 0-6.

If I don't have sales on Day 2, I get no result for Day 2.

What's the most efficient way to force a zero value for day 2?

Should I join to a temporary table or array of defined buckets? ['0','1','2','3','4','5','6']

Or is it better to insert zeros outside of MySQL, after I've done the query?

I am using MySQL, but this is a general SQL question.

Answer 1

In MySQL, you could simply use a derived table of numbers from 1 to 7, left join it with the table, then aggregate:

select d.day_of_week, sum(sales) AS total_sales
from (
    select 1 day_of_week union all select 2 union all select 3 union all select 4
    union all select 5 union all select 6 union all select 7
) d
left join table1 t1 on weekday(t1.transaction_date) = d.day_of_week
group by day_of_week

Very recent versions have the values(row...) syntax, which shortens the query:

select d.day_of_week, sum(sales) AS total_sales
from (values row(1), row(2), row(3), row(4), row(5), row(6), row(7)) d(day_of_week)
left join table1 t1 on weekday(t1.transaction_date) = d.day_of_week
group by day_of_week