Google Groups : comp.databases.ms-sqlserver

Google Groups : comp.databases.ms-sqlserver


Eddie Heard Mar 3, 11:49 am

I'm working on a fairly complex query, and to avoid exploding my brain
any further, I'm going to keep this simple with an example of what I'm
trying to do...

Let's say you had a table of customers and a table of orders
And you had data that looked like this:

Customers
CustID CustName
-------------------------------
1 Billy
2 John

Orders
CustID Amount PaidYN
-------------------------------
1 $2 1
2 $3 0
1 $4 1
1 $2 0
2 $5 0

And you wanted a report that looked like this:

Customer TotalPaid TotalUnpaid
------------------------------------------
Billy $6 $2
John $0 $8

How would you write a query to generate this report?





MADS Mar 3, 11:59 am

select c.CustId,
c.CustName,
sum(case when o.PaidYN = 1 then o.Amount else 0 end) as TPaid,
sum(case when o.PaidYN = 0 then o.Amount else 0 end) as TUnPaid
from Customers c, Orders o
where c.CustId = o.CustId
group by c.CustId, c.CustName

Reply



Thank you very much. It was an elegant solution. I plugged it into my
more complex (real) problem, and now it works great!