How to subtract two rows from two different selects?-CodePudding

I'm trying to subtract the total number shares for each symbol from the sell orders and the buy orders so I can have that total of shares owned.

buy = db.execute("SELECT symbol,SUM(shares) as total FROM negocios WHERE userid = ? and operation = 'buy' GROUP BY symbol");

sell = db.execute("SELECT symbol,SUM(shares) as total FROM negocios WHERE userid = ? and operation = 'sell' GROUP BY symbol")

I want a table like this with the values already subtracted:

symbol	total
CSCO	25
GOOG	6
NFLX	32

I was trying :

SELECT ((SELECT symbol,SUM(shares) as total FROM negocios WHERE userid = ? and operation = 'buy' GROUP BY symbol) - (SELECT symbol,SUM(shares) as total FROM negocios WHERE userid = ? and operation = 'sell' GROUP BY symbol) AS diff;

But I'm getting an error:

Parse error: sub-select returns 2 columns - expected 1

Table schema:

CREATE TABLE negocios (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, userid TEXT NOT NULL, symbol TEXT NOT NULL, operation TEXT CHECK( operation IN ('buy', 'sell')), price DECIMAL(10,2), time TIMESTAMP, shares DECIMAL(10,2), name TEXT);

CodePudding user response：

You can do it with a single query if you use conditional aggregation:

userid = 100 #variable that stores the user's id 
sql = """
SELECT symbol, 
       SUM(shares * CASE operation WHEN 'buy' THEN 1 WHEN 'sell' THEN -1 END) AS total 
FROM negocios 
WHERE userid = ? 
GROUP BY symbol
"""
result = db.execute(sql, (userid,))