SQL Group By Query?? 1

[DanKon]

I have a SQL Query Question that has been nagging me. I know exactly what I need to end up with, and I'm pretty sure it's possible through a stored procedure or a view (anything?), but I just don't know the syntax to get what I need.

Basically, I have a transaction table with the following relevant fields:

CustomerID, Debit_Amount, Credit_Amount, Description

The Debit_Amount column has the amounts for all charges made to the customer's account. If the transaction was a credit/payment, then Debit_Amount = 0. Likewise, for debits, Credit_Amount = 0. The Credit_Amount column has amounts for all discounts and payments toward the customer's account. A credit is anything that has a nonzero value in Credit_Amount and a Description *not equal* to 'Payment' - whereas, a Payment is defined as any nonzero value in Credit_Amount with 'Payment' as the Description.

I need to end up with a column for SUM(Debit_Amount), Sum(Credit_Amount), Sum(Payment_Amount) and Balance --

I guess the way to approach this would be to somehow create an additional column called Payment_Amount that equals the value of Credit_Amount when Description = 'Payment' and is otherwise 0. Likewise, the Credit_amount column would have to become 0 when the description = 'Payment' -- On top of that, there would have to be a GROUP BY CustomerID.

Is this possible? If you can help me with this, I'll be your best friend

Dan

 

[MeanGreen]

I think this query will work for you:

select customerid,sum(debit_amount) as DEBIT,
SUM(
case when description <> 'Payment' then Credit_Amount
else 0 end) as CREDIT_AMOUNT,
SUM(
case when description = 'Payment' then Credit_Amount
else 0 end) as PAYMENT_AMOUNT,
SUM(debit_amount)- Sum(Credit_amount) as BALANCE
from YourTable
group by customerid

Hope this helps.

 

[DanKon]

thanks so much!!!