I'm trying to generate seven rows for each date in the last seven days, and join with a query from transactions table. The aim is to have a table with each date, and the cumulative total of the quantity column in transactions from the first entry up to the date:
| date | stockOnDate |
|---------------|---------------|
| 2021-10-15 | 10 |
| 2021-10-16 | 3 |
| 2021-10-17 | 0 |
| 2021-10-18 | 9 |
| 2021-10-19 | 15 |
| 2021-10-20 | 15 |
| 2021-10-21 | 15 |
I can get the list of dates, and can join, but can't filter the nested queries:
SELECT v.*, t.*
FROM ( SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 7 DAY), t3*1000 t2*100 t1*10 t0)) AS `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 ) AS v
LEFT JOIN (SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate
FROM `transactions`
WHERE tDate <= v.`date`) AS t ON t.tDate = v.`date`
WHERE v.`date` >= DATE_SUB(NOW(),INTERVAL 7 DAY) AND v.`date` <= DATE(NOW())
But I'm receviing the following error:
#1054 - Unknown column 'tDate' in 'where clause'
If I replace WHERE tDate <= v.date with WHERE DATE(timestamp) <= v.date I get the same error for v.date - I can't seem to access the value of the parent tables.
I'm not great with MySQL but can't seem to find a solution, where am I going wrong?
Solution by ProGuru
Thanks to ProGuru's answer below, the below query works as expected (using <= instead of = in the JOIN was key)
SELECT b.date, SUM(a.quantity) AS stockOnDate
FROM (
SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 6 DAY), t3*1000 t2*100 t1*10 t0)) AS `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
) b
LEFT JOIN transactions a ON DATE(a.timestamp) <= b.date
WHERE b.date BETWEEN DATE(NOW()) - INTERVAL 6 DAY AND DATE(NOW())
AND a.organisationId = 1
GROUP BY b.date
ORDER BY b.date ASC
I can also change the GROUP BY to GROUP BY a.itemID, b.date to get the stock level on the given date for each itemId.
CodePudding user response：
To get the cumulative sum of quantity, the date join needs to use <=
Revised SQL
SELECT b.date, SUM(COALESCE(a.quantity, 0))
FROM (
SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 7 DAY), t3*1000 t2*100 t1*10 t0)) AS `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
) b
LEFT JOIN transactions a ON DATE(a.timestamp) <= b.date
WHERE b.date BETWEEN DATE(NOW()) - INTERVAL 6 DAY AND DATE(NOW())
GROUP BY b.date
ORDER BY b.date ASC
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=34f78e3d7c9225727ac2e728588759e2
CodePudding user response：
To use alias in where clause, you must encapsulate your query in another select.
Example :
SELECT * FROM
(SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate FROM `transactions`) seb
WHERE seb.tDate <= v.`date`
Update try this:
SELECT v.*, t.*
FROM ( SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 7 DAY), t3*1000 t2*100 t1*10 t0)) AS `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 ) AS v
LEFT JOIN (SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate FROM transactions) AS t ON t.tDate = v.`date`
WHERE t.tDate <= v.`date` and v.`date` >= DATE_SUB(NOW(),INTERVAL 7 DAY) AND v.`date` <= DATE(NOW())
here tDate can be used
CodePudding user response：
Firstly you need to understand that in this subquery
(SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate
FROM `transactions`
WHERE tDate <= v.`date`) AS t
there is nothing defined as date or even the alias v. You're basically telling the query to look for v.date when it doesn't exists in the subquery.
I think this is what you're looking for:
SELECT dt, IFNULL(SUM(quantity),0) AS stockOnDate
FROM (SELECT STR_TO_DATE(CONCAT(LEFT(NOW(),7),LPAD(seq,2,0)), '%Y-%m%d') dt
FROM seq_1_to_31) v
LEFT JOIN `transactions` t ON dt=DATE(timestamp)
AND DATE(timestamp) >= dt
GROUP BY dt