CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
weloytty
weloytty

Reputation: 6108

how to have a where clause in an aggregate function SQL Server

OK, I have a table that looks like this:

ID   AMOUNT      PAID
1    50.00       Y
2    100.00      N
3    200.00      Y

And I want to see something like:

Total     Due Paid
350.00    1   2

So my SQL would look like (in my head...it doesnt work that way, which is why I'm here )

select sum(amount)
,count(paid where paid='y') as due
,count(paid where paid='n') as paid 
from sometable where something=somethingelse

Upvotes: 2

Views: 342

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

One more option

SELECT SUM(AMOUNT) AS Total,
       COUNT(CASE WHEN PAID = 'Y' THEN PAID END) AS Paid,
       COUNT(CASE WHEN PAID = 'N' THEN PAID END) AS Due
FROM sometable
WHERE something = somethingelse

Demo om SQLFiddle

Upvotes: 1

user533832
user533832

Reputation: 

select sum(amount) as total, 
       sum(case paid when 'N' then 1 else 0 end) as due, 
       sum(case paid when 'Y' then 1 else 0 end) as paid
from sometable where something=somethingelse

Upvotes: 5

Related Questions