0

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: enter image description here

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.

1
  • MariaDB has pseudo tables like seq_0_to_99999 to do that crossjoin. In MySQL, I recommend building a table once and using it as needed.
    – Rick James
    Feb 23, 2023 at 17:26

2 Answers 2

0

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.

1
  • As an aside, LEFT JOIN book b ON 1 = 1 is like a cross join but ensures that rows from the left side (the lj derived table) are returned even when book is empty. If that is the intention, then it kind of makes sense. However, the very next join on ss.bookId = b.id would actually eliminate the output in that scenario, because an empty book table means that b.id is null, and value = null cannot evaluate to true in SQL. Therefore, you could just as well replace the above-mentioned left join with CROSS JOIN book b with exactly the same outcome.
    – Andriy M
    Feb 23, 2023 at 19:22
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.