CODEWITHSUNDEEP
sqloracle-database
Scott Holtzman
Scott Holtzman

Reputation: 27239

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?

Upvotes: 0

Views: 32

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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 ;

Upvotes: 1

sstan
sstan

Reputation: 36473

Move the SUM outside the CASE statement:

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

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

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

  FROM WORK_COMMISSION_RESULTS a

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

Upvotes: 1

Related Questions