SQL to summarise solar power generated by quarter-CodePudding

I have a single MySQL table with 10 years of solar panel generation data (every 10 minutes, where generation is > zero).

I have no idea how to construct a query that will give me four values per year row, each value representing the summed total for each quarter in that year.

My source table (DTP) schema looks like this:

#,  Field,      Type,           Null,   Key,    Default,    Extra
1,  'PWR',      'decimal(5,3)', 'NO',   '',     NULL,       ''
2,  'idDTP',    'int(11)',      'NO',   'PRI',  NULL,       'auto_increment'
3,  'DT',       'datetime',     'NO',   '',     NULL,       ''

One example I though might point me in the right direction looks like this (bear in mind this is partly modified from the source):

SELECT Year,SUM(Quarter1) AS Quarter1,SUM(Quarter2) AS Quarter2,SUM(Quarter3) AS Quarter3,SUM(Quarter4) AS Quarter4
FROM
(
    SELECT YEAR(DT) AS 'Year' ,
    Quarter1 = CASE(DATEPART(q, DTP.DT))
    WHEN 1 THEN SUM(DTP.DT)
    ELSE 0
    END,
    Quarter2 = CASE(DATEPART(q, DTP.DT))
    WHEN 2 THEN SUM(DTP.DT)
    ELSE 0
    END,
    Quarter3 = CASE(DATEPART(q, DTP.DT))
    WHEN 3 THEN SUM(DTP.DT)
    ELSE 0
    END,
    Quarter4 = CASE(DATEPART(q, DTP.DT))
    WHEN 4 THEN SUM(DTP.DT)
    ELSE 0
    END
 FROM DTP LEFT JOIN PWR ON DTP.DT = Customers.CustomerID
 LEFT JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderID
 GROUP BY CompanyName, YEAR(OrderDate), DATEPART(q, OrderDate)
 )C
 GROUP BY CompanyName,Year

I started trying to convert it to my scenario but without understanding it, I've landed myself in a half finished (assuming this is even on the right lines) mess and I'm not sure how to fix it. Any well explained hints or links would be welcome - flames, less so. ;oP

If I've got the wrong forum, please politely point out the right one - couldn't see a better alternative in the Stack-Echange list.

Thanks Small sample from first year of source data (Feb-Mar)

'160851', '2012-02-29 08:00:00', '0.030'
'160852', '2012-02-29 08:10:00', '0.066'
'160853', '2012-02-29 08:20:00', '0.072'
'160854', '2012-02-29 08:30:00', '0.060'
'160855', '2012-02-29 08:40:00', '0.090'
'160856', '2012-02-29 08:50:00', '0.102'
'160857', '2012-02-29 09:00:00', '0.084'
'160858', '2012-02-29 09:10:00', '0.132'
'160859', '2012-02-29 09:20:00', '0.144'
'160860', '2012-02-29 09:30:00', '0.138'
'160861', '2012-02-29 09:40:00', '0.150'
'160862', '2012-02-29 09:50:00', '0.174'
'160863', '2012-02-29 10:00:00', '0.174'
'160864', '2012-02-29 10:10:00', '0.162'

I can't enter a years worth of data, as it unsurprisingly exceeds the allowed count but it proceeds in a similar vein.

There's no meaningful output because I got out of my depth well before I'd approached anything like viable code.

CodePudding user response：

Define a new year-quarter column, named yq. Fill it with values like "2021-q4" and "2022-q1".

Now your problem is simple. Just compute SUM( ... ) with GROUP BY yq.

CodePudding user response：

You are missing some tables, but i kept them in the subquery.

the subwquery, would show the numbers for all quaters , for every company and year.

The outer query, would, sum them up into years without companies..

As you need more columns you can add them from comapanies orders or order details and add them in the inner SELECT and then add them also in the outer, to group them for them examle by orders or such

SELECT Year,SUM(Quarter1) AS Quarter1,SUM(Quarter2) AS Quarter2,SUM(Quarter3) AS Quarter3,SUM(Quarter4) AS Quarter4
FROM
(
    SELECT  CompanyName,YEAR(DT) AS 'Year' ,
    SUM(CASE WHEN DATEPART(q, DTP.DT)  =  1 THEN DTP.PWR ELSe 0 END)  Quarter1,
    SUM(CASE WHEN DATEPART(q, DTP.DT)  =  2 THEN DTP.PWR ELSe 0 END) Quarter2,
        SUM(CASE WHEN DATEPART(q, DTP.DT)  =  3 THEN DTP.PWR ELSe 0 END)  Quarter3,
            SUM(CASE WHEN DATEPART(q, DTP.DT)  =  4 THEN DTP.PWR ELSe 0 END) Quarter4
 FROM DTP LEFT JOIN PWR ON DTP.DT = Customers.CustomerID
 LEFT JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderID
 GROUP BY CompanyName, YEAR(OrderDate)
 ) C
 GROUP BY Year