I have a table:
MariaDB [energy_data]> describe sensor1_days;
+-----------+-----------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------------------+-------------------------------+
| timestamp | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| day | datetime | NO | PRI | NULL | |
| sum | float | NO | | NULL | |
+-----------+-----------+------+-----+---------------------+-------------------------------+
"day" represents the date for this day and in "sum" is that value for this one day.
Example / extract data:
"timestamp" "day" "sum"
...
"2022-09-28 00:01:01" "2022-09-27 00:00:00" "10,54"
"2022-09-27 00:01:01" "2022-09-26 00:00:00" "13,52"
...
"2022-08-02 00:01:01" "2022-08-01 00:00:00" "11,57"
...
"2021-09-17 00:01:01" "2021-09-16 00:00:00" "10,3"
...
"2021-08-16 00:01:01" "2021-08-15 00:00:00" "7,14"
...
"2021-07-15 00:01:01" "2021-07-14 00:00:00" "11,45"
...
"2017-02-17 00:01:01" "2017-02-16 00:00:00" "1,1"
"2017-02-16 00:01:01" "2017-02-15 00:00:00" "2,1"
"2017-02-15 00:01:01" "2017-02-14 00:00:00" "3,1"
...
I want to get the monthly sum for each year and can get it in an long list so far with query:
SELECT
YEAR(day) AS "Year",
MONTH(day) AS "Month",
SUM(sum) AS Total
FROM energy_data.sensor1_days
GROUP BY YEAR(day) , MONTH(day)
ORDER BY YEAR(day) ASC , MONTH(day) ASC
Result (for extract):
"Year" "Month" "Total"
...
"2022" "9" "24,06"
"2022" "8" "11,57"
...
"2021" "9" "10,3"
"2021" "8" "7,14"
"2021" "7" "11,45"
...
"2017" "2" "6,3"
...
But now I want it formated like this table view: (I have all data in one table and don't know how to formulate the SQL call)
Month 2017 2018 2019 2020 2021 2022
1 0 0 0 0 0 0
2 6,3 0 0 0 0 0
3 0 0 0 0 0 0
4 0 0 0 0 0 0
5 0 0 0 0 0 0
6 0 0 0 0 0 0
7 0 0 0 0 11,45 0
8 0 0 0 0 7,14 11,57
9 0 0 0 0 10,3 24,06
10 0 0 0 0 0 0
11 0 0 0 0 0 0
12 0 0 0 0 0 0
--> How must the Mysql Query be? (I have all data in one table and don't know how to formulate the SQL call, other solutions in the forum divers here and because of my limited sql knowledge I don't know how to formulate the SQL call)
