SQL to average different time scopes

I have a table (DTP) with a simple structure and around 200k rows

```+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| DT    | datetime     | YES  |     | NULL    |                |
| PWR   | decimal(5,3) | YES  |     | NULL    |                |
| Pix   | int(11)      | NO   | PRI | NULL    | auto_increment |
+-------+--------------+------+-----+---------+----------------+```
• DT = a date-time every 10 minutes (of daylight)
• PWR = kW power generation avg for the 10 minute period

I want to know daily, weekly, monthly and annual average power generation

I suspected the following was wrong even before I tried it but I can’t find help on how to structure my SQL to return these averages. (I’m anticipating one different query for each average per period.

```SELECT AVG(PWR) AS DailyAvgPwr
FROM DTP
WHERE (year(DT) AND month(DT) AND day(DT)) IN(
SELECT PWR, year(DT), month(DT), day(DT)
FROM DTP
GROUP BY year(DT), month(DT), day(DT)
ORDER BY year(DT), month(DT), day(DT);```

It would be great to have some guidance on at least one of the periods: I’m guessing I can extrapolate the answer to get the other period averages.
[in case it’s relevant, I’m using MariaDB, Server version: 10.5.12]

Contents

Method 1

I think this is a job for WITH ROLLUP. Try something like this.

```SELECT YEAR(DT) YR, MONTH(DT) mon, DAY(DT) dy,
AVG(PWR) kW
FROM DTP
GROUP BY YEAR(DT), MONTH(DT), DAY(DT) WITH ROLLUP```