MySQL: Group result of timestamp depending data in columns

Question

I have data like this for a period over 3 years:

TimeStamp	Consumption
2022-01-16 00:15:00	48
2022-01-16 00:30:00	38
2022-01-16 00:45:00	40
2022-01-16 01:00:00	346
2022-01-16 01:15:00	182
2022-01-16 01:30:00	38
2022-01-16 01:45:00	44
2022-01-16 02:00:00	53

I would like to query and group the sum of consumption over a month grouped by years. Every year shall have its own column like this:

Month	2020	2021	2022
1		616522	251669
2		661909
3		476928
4	340073	417943
5	343518	363774
6	333023	383071
7	376138	372729
8	451099	338588
9	333298	326093
10	399301	363540
11	421328	458276
12	553438	546010

All I already managed to query was:

SELECT
    DATE_FORMAT(TimeStamp, '%M %Y'),
    sum(Consumption)
FROM
    SolarEdge_EnergyDetails
GROUP BY
    date_format(TimeStamp, '%M %Y');

resulting in this:

DATE_FORMAT(TimeStamp, '%M %Y')	sum(Consumption)
April 2020	340073
April 2021	417943
August 2020	451099
August 2021	338588
December 2020	553438
December 2021	546010
February 2021	661909
January 2021	616522
January 2022	251669
July 2020	376138
July 2021	372729
June 2020	333023
June 2021	383071
March 2021	476928
May 2020	343518
May 2021	363774
November 2020	421328
November 2021	458276
October 2020	399301
October 2021	363540
September 2020	333298
September 2021	326093

How do I get my results sort per year in columns?

Answer 1

Would you be so kind to try this solution? I mean if you would like to get the sum of the years, this would be a solution. Grouping by only the year

SELECT
    DATE_FORMAT(TimeStamp, '%Y'),
    sum(Consumption)
FROM
    SolarEdge_EnergyDetails
GROUP BY
    date_format(TimeStamp, '%Y');

Answer 2

You are looking for pivot you can try to use outer join with condition aggregate function.

Query 1:

SELECT
    t1.Months 'Month',
    sum(CASE WHEN YEAR(t2.TimeStamp) = 2020 THEN t2.Consumption END) '2020',
    sum(CASE WHEN YEAR(t2.TimeStamp) = 2021 THEN t2.Consumption END) '2021',
    sum(CASE WHEN YEAR(t2.TimeStamp) = 2022 THEN t2.Consumption END) '2022'
FROM
(
    SELECT Months
    FROM 
    (   SELECT 1 Months
        UNION ALL 
        SELECT 2
        UNION ALL 
        SELECT 3
        UNION ALL 
        SELECT 4
        UNION ALL 
        SELECT 5
        UNION ALL 
        SELECT 6
        UNION ALL 
        SELECT 7
        UNION ALL 
        SELECT 8
        UNION ALL 
        SELECT 9
        UNION ALL 
        SELECT 10
        UNION ALL 
        SELECT 11
        UNION ALL 
        SELECT 12
    ) t1
) t1
LEFT JOIN SolarEdge_EnergyDetails t2 ON t1.Months = MONTH(t2.TimeStamp)
GROUP BY
    t1.Months

Results:

Answer 3

You can do it with a static query like thus, but you must change it every year

SELECT 
MONTH(ts) AS MONTH,
sum(IF(YEAR(ts) = 2020,con,NULL)) AS '2020',
sum(IF(YEAR(ts) = 2021,con,NULL)) AS '2021',
sum(IF(YEAR(ts) = 2022,con,NULL)) AS '2022'

FROM SolarEdge_EnergyDetails
GROUP BY MONTH(ts)
ORDER BY ts;

Or you change the column name to relativ like this:

SELECT 
MONTH(ts) AS MONTH,
sum(IF(YEAR(ts) = YEAR(now()),con,NULL)) AS 'this year',
sum(IF(YEAR(ts) = YEAR(now() - INTERVAL 1 YEAR),con,NULL)) AS 'last year',
sum(IF(YEAR(ts) = YEAR(now() - INTERVAL 2 YEAR),con,NULL)) AS '2 years ago'

FROM SolarEdge_EnergyDetails
WHERE YEAR(ts) >= YEAR(now() - INTERVAL 2 YEAR)
GROUP BY MONTH(ts)
ORDER BY ts;