Merge rows into one row - Orcale 11g Database-CodePudding

I have this SQL Statement here:

SELECT DATETIME, DWH_ACCOMODATIONS.NAME, SUM(NIGHTS) 
FROM dwh_data JOIN DWH_ACCOMODATIONS ON dwh_data.fk_accomodation = DWH_ACCOMODATIONS.CODE 
WHERE dwh_data.fk_accomodation = 998 AND DATETIME BETWEEN 201001 AND 201012
GROUP BY DWH_ACCOMODATIONS.NAME, DATETIME, NIGHTS;

This statement gives me this result:

DATETIME         NAME             SUM(NIGHTS)
--------------   --------------   --------------   
201011           Hotel 1<998>     12689
201012           Hotel 1<998>     18495
201012           Hotel 1<998>     4958
201012           Hotel 1<998>     37580
201011           Hotel 1<998>     85938
201011           Hotel 1<998>     27488
201011           Hotel 1<998>     9874

The problem as you can see is that datetime and one hotel name are duplicate in the table

Now I'd like to get a result like this:

DATETIME         NAME             SUM(NIGHTS)
--------------   --------------   --------------   
201011           Hotel 1<998>     1268949
201012           Hotel 1<998>     1869859

For sure the nights should also be summed up.

I already tried it with this in the GROUP BY:

GROUP BY ROLLUP(DWH_ACCOMODATIONS.NAME), ROLLUP(DATETIME), ROLLUP(NIGHTS)

CodePudding user response：

GROUP BY is wrong; you should remove NIGHTS from it.

  SELECT DATETIME, DWH_ACCOMODATIONS.NAME, SUM (NIGHTS) sum_nights
    FROM dwh_data
         JOIN DWH_ACCOMODATIONS
            ON dwh_data.fk_accomodation = DWH_ACCOMODATIONS.CODE
   WHERE     dwh_data.fk_accomodation = 998
         AND DATETIME BETWEEN 201001 AND 201012
GROUP BY DWH_ACCOMODATIONS.NAME, DATETIME

If you want to "merge" datetime values, then use listagg function:

  SELECT LISTAGG (datetime, ', ') WITHIN GROUP (ORDER BY datetime)
            AS datetimes,
         dwh_accomodations.name,
         SUM (nights)
            sum_nights
    FROM dwh_data
         JOIN dwh_accomodations
            ON dwh_data.fk_accomodation = dwh_accomodations.code
   WHERE     dwh_data.fk_accomodation = 998
         AND datetime BETWEEN 201001 AND 201012
GROUP BY dwh_accomodations.name