Mysql query to return a value for every day from X to X even if table has no data for that day

Question

I have this query:

SELECT 
    `sr`.`transactionDate`,
    `b`.`title`,
    `b`.`color`,
    ROUND(SUM(sr.digitalEarnings), 2) AS earnings
FROM
    `sales_report` AS `sr`
        INNER JOIN `sales_source` AS `ss` ON `sr`.`salesSourceId` = `ss`.`id`
        INNER JOIN `book` AS `b` ON `ss`.`bookId` = `b`.`id`
        LEFT JOIN `series` AS `s` ON `b`.`seriesId` = `s`.`id`
GROUP BY `ss`.`bookId` , `sr`.`transactionDate`
ORDER BY 4 DESC;

which returns:

Instead of only returning data for the days which exist in sales_report, I need one row per day, per b.title between 2 dates.

So if I queried for January, I'd have 31 entries for one book with zero earnings on the days which have no entries in sales_report.

I'm assuming left joining on a fake table with all the dates in or something like that might be the route to go down but I can't get my head around it.

Is this possible purely with mysql?

p.s My SQL query is dynamically generated in code so if a left join on a bunch of "generated data" is the way to go, this is an option. This is the avenue I'm currently exploring.

Edit: The question is similar to Results for every hour of the day even data if not present but this one is for SQL and I'd prefer to not use a temp table.

Edit:

I've just tried this:

SELECT 
    lj.gh,
    `sr`.`transactionDate`,
    `b`.`title`,
    `b`.`color`,
    ROUND(SUM(sr.digitalEarnings), 2) AS earnings
FROM
    (
            select gen_date as gh from 
            (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
             (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
             (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
             (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
             (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
             (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
            where gen_date between '2017-01-01' and '2017-01-31'
        ) as lj
        left join `sales_report` AS `sr` on sr.transactionDate = lj.gh
        INNER JOIN `sales_source` AS `ss` ON `sr`.`salesSourceId` = `ss`.`id`
        INNER JOIN `book` AS `b` ON `ss`.`bookId` = `b`.`id`
        LEFT JOIN `series` AS `s` ON `b`.`seriesId` = `s`.`id`
GROUP BY `ss`.`bookId` , `sr`.`transactionDate`, lj.gh
ORDER BY 1, b.title asc;

But I'm not quite seeing an entry per day for some reason... Ah: Its returning each book (even if no sales_report) but only when there is at least one entry of sales_report on that day which is down to the inner joins.

Answer 1

This seems to do the trick:

SELECT
    lj.gh,
    b.title,
    b.color,
    COALESCE(ROUND(SUM(sr.digitalEarnings), 2), 0) AS earnings
FROM
    (
        SELECT DATE_FORMAT(date_table.date, '%Y-%m-%d') AS gh
        FROM (
            SELECT DATE_SUB('2015-10-01', INTERVAL 1 DAY) + INTERVAL t4*10000 + t3*1000 + t2*100 + t1*10 + t0 DAY AS date
            FROM
                (SELECT 0 t0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t0,
                (SELECT 0 t1 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
                (SELECT 0 t2 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
                (SELECT 0 t3 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3,
                (SELECT 0 t4 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4
        ) date_table
        WHERE date_table.date BETWEEN '2015-10-01' AND '2018-10-31'
    ) lj
    LEFT JOIN book b ON 1 = 1
    INNER JOIN sales_source ss ON ss.bookId = b.id
    LEFT JOIN sales_report sr ON sr.salesSourceId = ss.id AND sr.transactionDate = lj.gh
    LEFT JOIN series s ON b.seriesId = s.id
GROUP BY b.id, lj.gh
ORDER BY lj.gh ASC, b.title ASC;

It's just not fast at all when querying a large date range. Around 14 seconds for 3 years data.

Answer 2

MySQL 8.0's manual page for recursive common table expressions has an example specifically about generating a date series.

In your case it would like like this:

WITH RECURSIVE dates (date) AS
(
  SELECT CAST('2017-01-01' AS DATE)
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date < '2017-01-31'
)
SELECT * FROM dates;

Then you can outer join that to your sales_report and other tables. I'll leave that to you.