CODEWITHSUNDEEP
mysql
Zainul Abideen
Zainul Abideen

Reputation: 1900

How to Perform Subtraction in SQL query based on some conditions?

I want to sum up qty*price where type is 'in' and then subtract the sum of qty*price where type is 'out'. Something like this. SELECT cid, name, SUM(paid_amt), (SELECT SUM(qty*price) WHERE type = 'in' - SELECT SUM(qty*price) WHERE type = 'out'), type FROMtransactionsGROUP BY cid Here's is my SQL query.

Upvotes: 2

Views: 1702

Answers (1)

Jacobm001
Jacobm001

Reputation: 4539

You can solve your problem by using two different sum expressions, each with a slightly different case statement inside of it. The idea is that your case statement only returns a value to the aggregate sum expression if it's of the correct typing.

select
  cid
  , sum(case when type = "in" then qty*price else 0 end) 
  - sum(case when type = "out" then qty*price else 0 end)
from
  your_table
group by
  cid

Upvotes: 3

Related Questions