CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
user8251888
user8251888

Reputation:

sql query for difference of same column

This is my database showing debit of my sales. here column type takes 0 as due amount and 1 as paid amount. I'm trying to calculate remaining due.

code Name   due bill date        type
--------------------------------------
113  ABC    30  9   2018-04-03    0
113  ABC    7   10  2018-04-03    0
113  ABC    20  11  2018-04-05    1
114  XYZ    25  25  2018-05-06    0

This is my desired table when 20 is subtracted from 37

code Name      due 
------------------
113 ABC        17
114 XYZ        25

I've used this query 

select code, name,SUM(amount) from Debit group by code, name, type

what will be the correct query for above problem? thanks in advance

Upvotes: 2

Views: 810

Answers (3)

Emdad
Emdad

Reputation: 832

You can also try the following query

SELECT A.code, A.name, (CASE WHEN A.due>A.payment THEN a.due-A.payment
                        CASE WHEN A.due>0 THEN A.due ELSE 0 END) AS due FROM 
(SELECT code,name, sum(case when type = 1 then due end) due, 
                   sum(case when type = 0 then due end) payment 
FROM [table] GROUP BY code,name) A

Upvotes: 1

Thom A
Thom A

Reputation: 95989

This can use solved by removing [Type] from the GROUP BY clause, and add a CASE expression:

SELECT code,
       [name],
       SUM(CASE [Type] WHEN 0 THEN 1 ELSE -1 END * amount) AS due
FROM Debit
GROUP BY code,
         [name];

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You want something like this:

select code, name,
       sum(case when type = 0 then due else - due end) as due
from t
where type in (0, 1)
group by code, name;

Upvotes: 4

Related Questions