How to make a custom group in sql

Question

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

sql oracle

Accessdenier 07 Feb 13 at 5:12 am

source to share

2 answers

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 07 Feb 13 at 5:21 am

source to share

Orangecrush · Accepted Answer · 2013-02-07T05:19:50+0000

This should work,

select "id",sum(decode(type,'sell',-"amount","amount")) total
from table1
group by "id"

SQL FIDDLE OUTPUT

How to make a custom group in sql

More articles: