Hi, the sql as written is wanting me to group by Broker_Reason as well as Comments (which is the broker's name). I only want to summarize by Comments. I've attempted to max and min for Broker_Reason and for the entire case statement, but not getting results I'm after. Can you tell me how to summarize commission at the Broker level without breaking out by broker reason codes as well?
Select (Case when (b.COMMENTS) = 'BrokerX' and (a.BROKER_REASON) = '4' then 'Different Broker' else (b.COMMENTS) end) 'Broker',
from TS_ORDER o join TS_ORDER_ALLOC a on o.ORDER_ID = a.ORDER_ID
join CSM_SECURITY s on o.SEC_ID = s.SEC_ID
join CS_BROKER b on b.BKR_CD = COALESCE(a.BKR_OF_CREDIT, a.STEP_OUT_BROKER, a.EXEC_BROKER)
where o.TRADE_DATE >= '1/1/2019' and o.trade_date <= '1/08/2019' and o.STATUS = 'acct'
and s.SEC_TYP_CD in ('ADR','COM','FSTK')
Group by b.COMMENTS
Having sum(a.PRIN_SETTLE_FX_RATEa.COMMISION_AMT) is not null