SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case statement grouping problem		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

250 Posts
Posted - 11/19/2012 :  08:56:40  Show Profile  Reply with Quote
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
Edited by - masond on 11/19/2012 09:15:57

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/19/2012 :  09:13:40  Show Profile  Reply with Quote
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

250 Posts
Posted - 11/19/2012 :  09:17:02  Show Profile  Reply with Quote
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

250 Posts
Posted - 11/19/2012 :  09:19:29  Show Profile  Reply with Quote
I think i need to put a sum before the case statement
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/19/2012 :  09:29:18  Show Profile  Reply with Quote
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

250 Posts
Posted - 11/19/2012 :  09:32:36  Show Profile  Reply with Quote
hi Sunitabeck

That seemed to of worked :)
Edited by - masond on 11/19/2012 09:37:03
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000