Get the sum of rows using sql query

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.

Answer

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;