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]

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0