Scott Holtzman Scott Holtzman - 6 months ago 14
SQL Question

Include Conditions to Sum for Two Fields in Group By Select Statement

I am pretty new to Oracle SQL (but have some basic SQL knowledge). Consider the following data structure:

Region Plan Code SM_Name Charge Commission
EMEA SM abc john smith DARK 10
Asia SM def bob jones MAKER 40
AmericasSM ghi chris eng TAKER 30
AmericasSM xyz bob marks FUND 40
Asia SM def brian bee DARK 10


I want to create a SELECT statement that will Sum the Commissions by Charge and place it in two separate columns.

So like this

Region Plan Code SM_Name DIRECT TRX
EMEA SM abc john smith 10
Asia SM def bob jones 40
AmericasSM ghi chris eng 30
AmericasSM xyz bob marks 40
Asia SM def brian bee 10


I am attempting to use CASE statements to create this from the research I have done, but it's not quite working. My SQL is below:

SELECT a.REGION, 'SM', a.TRADING_CODE, a.SM,

CASE
WHEN a.CHARGE = 'FUND' THEN SUM(a.COMMISSION)
END AS DIRECT,

CASE
WHEN a.CHARGE IN ('DARK', 'MAKER', 'TAKER') THEN SUM(a.COMMISSION)
END AS TRX

FROM WORK_COMMISSION_RESULTS a

GROUP BY a.REGION, 'SM', a.TRADING_CODE, a.SM;


I get the error "not a GROUP BY expression" when running the code. I understand that maybe IF statements may work as well, but I am a bit lost.

Can anyone assist in getting me in the right direction?

Answer

Use conditional aggregation:

select Region, Plan, Code, SM_Name,
       sum(case when charge = 'FUND' then Commission end) as Direct,
       sum(case when charge <> 'FUND' then Commission end) as TRX
from WORK_COMMISSION_RESULTS wcr
group by Region, Plan, Code, SM_Name ;