How can I get the average price on Monday grouped by time (every hour)
Table looks like:
| date | price |
|---|---|
| 2023-01-15 23:00:00 | 0.13 |
| 2023-01-15 22:00:00 | 0.18 |
| 2023-01-15 21:00:00 | 0.16 |
| 2023-01-15 20:00:00 | 0.20 |
| .... | |
| 2022-01-01 00:00:00 | 0.15 |
I tried query below:
SELECT date, AVG(price) as 'Monday'
FROM table
WHERE YEAR(date) = '2022' AND WEEKDAY(date) = 1
GROUP BY date_format(date, '%HH:%mm')
But it returns only the first monday of each month.
Expected result should be like this (probably I have to create 7 queries, each for one weekday):
CodePudding user response：
This should do the trick:
SELECT DATE_FORMAT(MIN(`date`), "%Y-%m-%d"), HOUR(`date`), AVG(`price`) as 'Monday'
FROM `table`
WHERE YEAR(`datetime`) = "2023" AND WEEKDAY(`datetime`) = 0
GROUP BY HOUR(`datetime`);