Help With Case - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Help With Case

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-26 : 09:15:39

Keith writes "Here is a sql statement that works but I need one additional thing that I can't figure out how to do.

select paymentname as name,
sum(CASE
WHEN paymentname = 'cash' THEN amount
ELSE amount + tip
END) as idealamount
FROM payments
GROUP BY paymentname

I need to produce a record set of payments plus tips for the payment type except for cash payments where I need the payment minus all tips for all payments types. This sql statements does it except the cash part.

Any help would be much appreciated."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-26 : 09:30:41

Thios seems t obe what you are asking for but I doubt if it's what you want.

select paymentname as name,
sum(CASE
WHEN paymentname = 'cash' THEN amount ELSE amount + tip
END) - max(case when paymentname = 'cash' then tips.tipsum else 0 end)
FROM payments, (select tipsum = sum(tips) from payments) as tips
GROUP BY paymentname

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources