Group by question

1

I am trying to summarize my information. I have the following table

Date
Amount
D/C (for Debit or Credit)

The amounts that are Debits need to be positive and the one that are Credits need to be negative. So I do the following

SELECT
Date
CASE WHEN D/C=D THEN Sum (Amount)
ELSE -Sum(Amount) as Amount

GROUP BY Date, D/C

The problem with this approach is that I get 2 rows for each date, one for Debits and one for Credits. I would like to only have one line which would be the summation of Debits and Credits (credits being negative).

Please advise how I would go about it. Thank you.

2

Hi @martinmix,

Please try this one,

SELECT Date,SUM(Amount) FROM (
SELECT
Date
CASE WHEN D/C=D THEN Sum (Amount)
ELSE -Sum(Amount) as Amount
FROM TableName
GROUP BY Date, D/C ) A

Thanks.

1 Like
3

I would think it would be like this:

SELECT Date, SUM(CASE [D/C] WHEN 'C' THEN -1 * Amount ELSE Amount END) As Amount
FROM TableName
GROUP BY Date
'

1 Like
4

Both work but Bob's is more straight forward. Thank you both!!!