How to make a custom group in sql
Here is a table:
ID TYPE AMOUNT
-- ---- ------
1 sell 50
1 sell 100
1 buy 200
2 sell 50
2 buy 100
How to write a SQL query that returns the following result:
ID TOTAL
-- -----
1 50
2 50
I think I need it group by id
, but I'm not sure how to do the "subtraction" on the "sell".
My db is an oracle by the way.
Thank you Relationship
+3
Accessdenier
source
to share
2 answers
This should work,
select "id",sum(decode(type,'sell',-"amount","amount")) total
from table1
group by "id"
SQL FIDDLE OUTPUT
+1
Orangecrush
source
to share
The answer is simple.
SELECT id, SUM(
CASE type
WHEN 'Buy' THEN amount
WHEN 'Sell' THEN -amount
END
) AS TOTAL
FROM tbl
GROUP BY id
+2
Rachcha
source
to share