CUBE Statement to merge rows into one row

I have a Table like this:

dwh_data:

 DATETIME        FK_FEDERAL_STATE     FK_ACCOMODATION      ARRIVALS     NIGHTS
--------------- -------------------- ------------------- ---------- ----------
    200010               W96-3           60                8012      24724
    200010               W96-3           61                2283       7715
    200010               W96-3           86                 674       4171
    200010               W96-3           87                 329       1998

HINT: Datetime = first 4 numbers = year last two numbers = month

This is my statement:

SELECT SUBSTR(datetime,1,4), dwh_accomodations.name, SUM(NIGHTS)
FROM dwh_data
     JOIN DWH_ACCOMODATIONS
        ON dwh_data.fk_accomodation = DWH_ACCOMODATIONS.CODE
  GROUP BY CUBE(datetime, dwh_accomodations.name)
  ORDER BY datetime;

My statement gives me this result:

 SUBS NAME                                               SUM(NIGHTS)
---- -------------------------------------------------- -----------
1998 ACC_TYPE1                                               104506
1998 ACC_TYPE2                                               150524
1998 ACC_TYPE1                                               399419

The problem there is that there are duplicate names for one year. SUBS = Year

I want to get all names with the same value into one row. The result should look like this then:

 SUBS NAME                                               SUM(NIGHTS)
---- -------------------------------------------------- -----------
1998 ACC_TYPE1                                               504925
1998 ACC_TYPE2                                               150524

CodePudding user response：

Also group by the year?

SELECT 
  SUBSTR(datetime,1,4) AS year
, acco.name
, SUM(NIGHTS) AS Total
FROM dwh_data AS data
JOIN DWH_ACCOMODATIONS AS acco
  ON acco.CODE = data.fk_accomodation
GROUP BY CUBE(SUBSTR(datetime,1,4), acco.name)
ORDER BY year;

Simplyfied test

create table test(
 yearmonth char(6), 
 grp char(1), 
 col number
)

insert all
into test (yearmonth, grp, col) values ('202110', 'A', 10)
into test (yearmonth, grp, col) values ('202111', 'A', 20)
into test (yearmonth, grp, col) values ('202012', 'B', 40)
select 1 from dual;

select SUBSTR(yearmonth,1,4) as year, grp, sum(col) as tot
from test
group by SUBSTR(yearmonth,1,4), grp
order by year

YEAR | GRP | TOT
:--- | :-- | --:
2020 | B   |  40
2021 | A   |  30

select SUBSTR(yearmonth,1,4) as year, grp, sum(col) as tot
from test
group by rollup(SUBSTR(yearmonth,1,4), grp)
order by year

YEAR | GRP  | TOT
:--- | :--- | --:
2020 | B    |  40
2020 | null |  40
2021 | A    |  30
2021 | null |  30
null | null |  70

select SUBSTR(yearmonth,1,4) as year, grp, sum(col) as tot
from test
group by cube(SUBSTR(yearmonth,1,4), grp)
order by year

YEAR | GRP  | TOT
:--- | :--- | --:
2020 | B    |  40
2020 | null |  40
2021 | A    |  30
2021 | null |  30
null | A    |  30
null | B    |  40
null | null |  70

db<>fiddle here