Get the sum of rows using sql query

Question

Suppose I have this table structure:

CREATE TABLE TEST 
(
    X VARCHAR2(1),  -- firstName
    Y VARCHAR2(1),  -- lastName
    Z NUMBER(3),    -- quantity
    W VARCHAR2(1)   -- B=Buy or S=Sell 
);

INSERT INTO TEST(x, y, z, w) VALUES ('a', 'b', 3, 'S');
INSERT INTO TEST(x, y, z, w) VALUES ('a', 'b', 5, 'B'); -- sum of 3 S + 5 B = 2 B
INSERT INTO TEST(x, y, z, w) VALUES ('c', 'd', 10, 'S');
INSERT INTO TEST(x, y, z, w) VALUES ('c', 'd', 2, 'B'); -- sum of 10 S + 2 B = 8 S

I want to sum the quantity for the same name and last name and get the result as below

X   Y   sum     W
------------------
a   b   2       B
c   d   8       S

Is it possible to get this result in a single SQL query ?

it is easy to get the SUM with the GROUP BY but did not find a way to keep the Buy and Sell column at the same time.

the column W denotes that it is either buy or a sell order for the quantity; — Meninx - メネンックス
– Meninx - メネンックス, Commented Jun 23, 2021 at 19:21

Gordon Linoff · Accepted Answer · 2021-06-23 19:21:46Z

1

Oh, I get it. I think you want aggregation with some additional conditional logic:

select x, y,
       abs(sum(case when w = 'B' then z else - z end)) as sum,
       (case when sum(case when w = 'B' then z else - z end) >= 0
             then 'B' else 'S'
        end)
from test
group by x, y;

answered Jun 23, 2021 at 19:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Meninx - メネンックス Over a year ago

Yes this answers the question. Thanks !

Collectives™ on Stack Overflow

Get the sum of rows using sql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related