How to find the profit and loss of a stock in SQL Table

sql

I have a SQL table with columns (TRANSACTION_ID, STOCK_NAME, STOCK_ID, VALUE, TYPE). The table represents the investment in different stocks as transactions. I need to get the SQL query to find the total profit/loss in each of the stocks.

SQL TABLE

1|REL|2624|6653|sell
2|INFY|7660|3064|buy
3|INFY|4361|3117|buy
4|ITC|7292|6865|buy
5|INFY|2187|2618|buy
6|ITC|7292|7313|sell
7|INFY|3478|3196|sell
8|INFY|3472|3105|sell
9|REL|4472|7398|sell
10|INFY|7660|3351|sell
11|REL|4472|7350|buy
12|REL|5833|8240|buy
13|REL|5833|7937|sell
14|INFY|2187|2487|sell
15|REL|7198|7998|sell
16|INFY|3478|2944|buy
17|REL|2624|6985|buy
18|REL|7198|7645|buy
19|INFY|3472|3046|buy
20|INFY|4361|3384|sell

STOCK_ID represents the id of that stock and TYPE represent whether the transaction is a buy or sell. For each STOCK_ID there will be exactly 1 buy transaction and 1 sell transaction. I need to find the profit or loss for each STOCK as follows

RESULT

ITC|448
INFY| 734
REL| -234

I have the logic and formula but I don't know how to convert it to SQL Query.

MY LOGIC for each STOCK_NAME, find sum(profit/loss) for STOCK_ID as (TYPE='sell') - (TYPE='buy').

for example

4|ITC|7292|6865|buy
6|ITC|7292|7313|sell

Profit/Loss of ITC = 7313-6865 = 448

This is the SQL Query for replicating the data

BEGIN TRANSACTION;

/* Create a table called NAMES */
CREATE TABLE stocks(id integer PRIMARY KEY, symbol text, stock_id integer, value integer, typ text);

/* Create few records in this table */
INSERT INTO stocks VALUES(1,'REL', 2624, 6653, 'sell');
INSERT INTO stocks VALUES(2,'INFY', 7660, 3064, 'buy');
INSERT INTO stocks VALUES(3,'INFY', 4361, 3117, 'buy');
INSERT INTO stocks VALUES(4,'ITC', 7292, 6865, 'buy');
INSERT INTO stocks VALUES(5,'INFY', 2187, 2618, 'buy');
INSERT INTO stocks VALUES(6, 'ITC', 7292, 7313, 'sell');
INSERT INTO stocks VALUES(7, 'INFY', 3478, 3196, 'sell');
INSERT INTO stocks VALUES(8, 'INFY', 3472, 3105, 'sell');
INSERT INTO stocks VALUES(9, 'REL', 4472, 7398, 'sell');
INSERT INTO stocks VALUES(10, 'INFY', 7660, 3351, 'sell');
INSERT INTO stocks VALUES(11, 'REL', 4472, 7350, 'buy');
INSERT INTO stocks VALUES(12, 'REL', 5833, 8240, 'buy');
INSERT INTO stocks VALUES(13, 'REL', 5833, 7937, 'sell');
INSERT INTO stocks VALUES(14, 'INFY', 2187, 2487, 'sell');
INSERT INTO stocks VALUES(15, 'REL', 7198, 7998, 'sell');
INSERT INTO stocks VALUES(16, 'INFY', 3478, 2944, 'buy');
INSERT INTO stocks VALUES(17, 'REL', 2624, 6985, 'buy');
INSERT INTO stocks VALUES(18, 'REL', 7198, 7645, 'buy');
INSERT INTO stocks VALUES(19, 'INFY', 3472, 3046, 'buy');
INSERT INTO stocks VALUES(20, 'INFY', 4361, 3384, 'sell');
COMMIT;

/* Display all the records from the table */

Solution 1:

Use a nested query. One to sum up the adds and subtracts, then one to combine them:

SELECT symbol, SUM(value) AS profit 
FROM (
    SELECT symbol, typ, CASE
        WHEN typ='buy' THEN -SUM(value) 
        WHEN typ='sell' THEN SUM(value) 
    END AS value 
    FROM stocks 
    GROUP BY symbol, typ
) sub
GROUP BY symbol;

Output:

REL|-234
INFY|734
ITC|448

And, by the way, kudos for including your data set as an executable query. That made my job a lot easier.

(For MySQL, the subquery must have an alias ['sub' in this example]. That isn't required for the other SQL products.)

How to find the profit and loss of a stock in SQL Table

Solution 1:

Related

Recent Posts