MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE transactions (id INT, `date` DATE, amount INT);
INSERT INTO transactions VALUES 
(1,	'2021-04-08',	500),
(2,	'2021-04-08',	600),
(3,	'2021-04-10',	350);
SELECT * FROM transactions;

Records: 3  Duplicates: 0  Warnings: 0




id
date
amount


1
2021-04-08
500

2
2021-04-08
600

3
2021-04-10
350


SET @outside := '2021-04-08,2021-04-09,2021-04-10,2021-04-11,2021-04-12';
WITH RECURSIVE
cte AS ( SELECT SUBSTRING_INDEX(@outside, ',', 1) `date`,
                TRIM(LEADING ',' FROM TRIM(LEADING SUBSTRING_INDEX(@outside, ',', 1) FROM @outside)) slack
         UNION ALL
         SELECT SUBSTRING_INDEX(slack, ',', 1),
                TRIM(LEADING ',' FROM TRIM(LEADING SUBSTRING_INDEX(slack, ',', 1) FROM slack))
         FROM cte
         WHERE slack != '' )
SELECT `date`, COALESCE(SUM(transactions.amount), 0) `sum`
FROM cte
LEFT JOIN transactions USING (`date`)
GROUP BY `date`
ORDER BY `date`


date
sum


2021-04-08
1100

2021-04-09
0

2021-04-10
350

2021-04-11
0

2021-04-12
0


SELECT `date`, COALESCE(SUM(transactions.amount), 0) `sum`
FROM JSON_TABLE(CONCAT('["', REPLACE(@outside, ',', '","'), '"]'),
                "$[*]" COLUMNS (`date` DATE path "$")) jsontable
LEFT JOIN transactions USING (`date`)
GROUP BY `date`
ORDER BY `date`


date
sum


2021-04-08
1100

2021-04-09
0

2021-04-10
350

2021-04-11
0

2021-04-12
0

date	sum
2021-04-08	1100
2021-04-09	0
2021-04-10	350
2021-04-11	0
2021-04-12	0