September 9, 2008 at 12:37 pm
select trans_Date,'MD' as org,
case when rate_cat = 'INTER'then sum(trans_amt)*0.1 when rate_sub_cat='IR'
then sum(trans_amt)*0.1 else sum(trans_amt) end as margin
from history_consstat
where s_loc_id in (03,09) and
trans_sub_type2 in ('CONS','CUS','DSR','GASL')
group by trans_Date
I want to return this statement without having to group by the columns in my case statement (in this instance rate_cat and rate_sub_cat).
Any help will be appreciated
September 9, 2008 at 12:46 pm
If you are looking for a single sum, you need to do the case inside the aggregate:
select trans_Date,'MD' as org,
--case when rate_cat = 'INTER'then sum(trans_amt)*0.1 when rate_sub_cat='IR'
--then sum(trans_amt)*0.1 else sum(trans_amt) end as margin
SUM(CASE WHEN rate_cat = 'INTER' THEN trans_amt * 0.1
WHEN rate_sub_cat='IR' THEN trans_amt * 0.1
ELSE trans_amt END)
from history_consstat
where s_loc_id in (03,09) and
trans_sub_type2 in ('CONS','CUS','DSR','GASL')
group by trans_Date
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply