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 |