Merge rows into one row - SQL 11g-CodePudding

Well, I have a table like this:

     ID DATETIME             FK_FEDERAL_STATE     FK_ACCOMODATION   ARRIVALS     NIGHTS
---------- -------------------- -------------------- --------------- ---------- ----------
    123185 200010               W96-5                             61       2433      16111
    123186 200010               W96-5                             86       3718      30319
    123187 200010               W96-5                             87       1100       8487

One hint: the Datetime = first 4 number = year last 2 numbers = month

So far so good.

This is my SQL Statement:

SELECT DATETIME, SUM(NIGHTS) AS NIGHTS FROM dwh_data GROUP BY ROLLUP(DATETIME);

This gives me every DATETIME loooking like this:

DATETIME                 NIGHTS
-------------------- ----------
202008                 17994407
202009                  9895439
202010                  4347704
202011                  1075204

As you can see my SQL query shows the nights of each Month. Is it possible to sum up all nights from a year so f.e.

    DATETIME                 NIGHTS
    -------------------- ----------
    2018                 117994407
    2019                  19895439
    2020                  14347704

CodePudding user response：

Convert to a date, and then group by year of that converted date:

GROUP BY YEAR(TO_DATE(DATETIME, 'YYYYMM'))

Or you could group by a substring which only looks at those first 4 characters:

GROUP BY SUBSTR(DATETIME, 1, 4)

CodePudding user response：

You can try GROUPING SETS to get both subtotals by YEAR and DATETIME.

SELECT SUBSTR(DATETIME, 1, 4) year, DATETIME, SUM(NIGHTS) AS NIGHTS 
FROM dwh_data 
GROUP BY GROUPING SETS ((SUBSTR(DATETIME, 1, 4), DATETIME), (SUBSTR(DATETIME, 1, 4)) ) ;