How to sum up two unique records and display it as a new record-OracleSQL?-CodePudding

I am trying to merge two records in my table and then represent it as a new record.

Here is my table:

date|flag|bu|volume|calls
14-Nov-21,1,accounting,50,10
14-Nov-21,2,InfoSec,111,106
14-Nov-21,3,IT,500,100
14-Nov-21,4,management,70,60
14-Nov-21,5,consulting,5,45

I want the table to look like this when I run my query:

date|flag|bu|volume|calls
14-Nov-21,1,accounting,50,10
14-Nov-21,2,InformationTechnology,611,206
14-Nov-21,4,management,70,60
14-Nov-21,5,consulting,5,45

As you can see, I merged InfoSec and IT records into one new record. Any ideas or suggestion as how to do this?

Here is what my query looks like so far:

with bc as (select * from table1)
select date,flag,case when bu='IT' and bu='InfoSec' then 'InformationTechnology' else bu end as bu,volume,calls

CodePudding user response：

You could aggregate using a CASE expression which places InfoSec and IT into the same bucket:

SELECT
    date,
    CASE WHEN flag IN (2, 3) THEN 2 ELSE flag END AS flag,
    CASE WHEN bu IN ('InfoSec', 'IT') THEN 'InformationTechnology'
         ELSE bu END AS bu,
    SUM(volume) AS volume,
    SUM(calls) AS calls
FROM table1
GROUP BY
    date,
    CASE WHEN flag IN (2, 3) THEN 2 ELSE flag END,
    CASE WHEN bu IN ('InfoSec', 'IT') THEN 'InformationTechnology'
         ELSE bu END;

CodePudding user response：

You need a CASE expression that merges the 2 values of bu:

SELECT date,
       MIN(flag) flag,
       CASE 
         WHEN bu IN ('InfoSec', 'IT') THEN 'InformationTechnology'
         ELSE bu 
       END bu,
       SUM(volume) volume,
       SUM(calls) calls
FROM tablename
GROUP BY date,
         CASE 
           WHEN bu IN ('InfoSec', 'IT') THEN 'InformationTechnology'
           ELSE bu 
         END;