Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case statement grouping problem

Author  Topic 

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-11-19 : 08:56:40
Hey guys

I am hoping you will be able to help me

I am after a one liner per every fdmsaccountno ,

however with the current query i am producing 193 lines

Any way i can achieve a one liner on a case statement

My query is

SELECT
case
when merch_purch_fees < 0 then SUM ([Merch_Purch_Fees])
when merch_purch_fees > = 0 then SUM ([Merch_Purch_Fees]) + sum([Per_Tran_Fees]) end as MSCTEST
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]
where FDMSAccountNo ='878031202889'
group by merch_purch_fees

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 09:13:40
quote:
I am hoping you will be able to help me

I am after a one liner per every ,

however with the current query i am producing 193 lines


After "every ____?" You didn't give the column name.

Try removing the "GROUP BY merch_purch_fees".
If you want one row for each FDMSAccountNo, use "GROUP BY FDMSAccountNo";
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-11-19 : 09:17:02
hi sunitabeck

when i change the query to

SELECT distinct [Merch_Purch_Fees],
case when merch_purch_fees < 0 then SUM ([Merch_Purch_Fees])
when merch_purch_fees > = 0 then SUM ([Merch_Purch_Fees]) + sum([Per_Tran_Fees]) end as MSCTEST
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]
where FDMSAccountNo ='878031202889'
group by FDMSAccountNo


i get this error msg

Msg 8120, Level 16, State 1, Line 2
Column 'FDMS.dbo.Fact_Omnipay_Profitability.Merch_Purch_Fees' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-11-19 : 09:19:29
I think i need to put a sum before the case statement
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 09:29:18
Yes, you are right:
SELECT SUM(
CASE
WHEN merch_purch_fees < 0 THEN [Merch_Purch_Fees]
WHEN merch_purch_fees > = 0 THEN [Merch_Purch_Fees] + [Per_Tran_Fees]
END
) AS MSCTEST
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]
WHERE FDMSAccountNo = '878031202889'
GROUP BY
merch_purch_fees

Also, if there is a possibility that [Per_Tran_Fees] would be null (or merch_purch_fees would be null), you should account for that, for example like this:
WHEN COALESCE(merch_purch_fees,0) > = 0 THEN COALESCE([Merch_Purch_Fees],0) +  COALESCE([Per_Tran_Fees],0)
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-11-19 : 09:32:36
hi Sunitabeck

That seemed to of worked :)
Go to Top of Page
   

- Advertisement -