WHERE clause help needed - 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

General SQL Server Forums

New to SQL Server Programming

WHERE clause help needed

Author

Topic

sqlrob
Starting Member

2 Posts

Posted - 2013-03-17 : 10:34:45

SELECT SUM(Amount) AS Sales
FROM Bank
WHERE
Class = 'incomecc' AND Class ='incomecheck'

want to total the Amount column when the Class column has incomecc and incomecheck...this does not work

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-17 : 10:47:19

Class = 'incomecc' AND Class ='incomecheck' <-- That will always return false. Perhaps you want to use OR rather than AND?

SELECT SUM(Amount) AS Sales
FROM Bank
WHERE 
Class = 'incomecc' OR Class ='incomecheck'

That will add up the amounts for the two classes 'incomecc' and 'incomecheck'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-03-17 : 10:49:48

SELECT SUM(CASE WHEN Class in ('incomecc','incomecheck') THEN Amount ELSE 0 END)
FROM Bank

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-03-17 : 10:58:06

Actually, I think there's something else you're grouping on here? If you want Amounts of people who paid by check and cc (not just one or the other),then you'd need something like

SELECT customer, SUM(Amount)
FROM bank
GROUP BY customer
having sum(case when Class = 'incomecc' then Amount else 0 end) > 0
and sum(case when Class = 'incomecheck' then Amount else 0 end) > 0

Jim

Everyday I learn something that somebody else already knew

sqlrob
Starting Member

2 Posts

Posted - 2013-03-17 : 11:02:31

thank you --great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources