SQL select statement (SQLite)

Question

I have the following table:

$ sqlite3 :memory:
BEGIN TRANSACTION;
CREATE TABLE trades (date, stock, buyer, seller, quantity);
INSERT INTO "trades" VALUES(1,'X','A','B',10);
INSERT INTO "trades" VALUES(1,'X','A','C',10);
INSERT INTO "trades" VALUES(1,'Y','C','A',20);
INSERT INTO "trades" VALUES(2,'X','B','A',10);
INSERT INTO "trades" VALUES(2,'Y','B','A',10);
INSERT INTO "trades" VALUES(2,'Z','D','E',11);
COMMIT;

select * from trades;
date        stock       buyer       seller      quantity  
----------  ----------  ----------  ----------  ----------
1           X           A           B           10        
1           X           A           C           10        
1           Y           C           A           20        
2           X           B           A           10        
2           Y           B           A           10        
2           Z           D           E           11

I wish to make a summary for each day and each stock, containing the total amount of bought and sold stocks for each broker, i.e. the aggregated activity for each broker for each stock and each day. The output I am looking for is something like this:

select ...magic statement ... from trades;
date        stock       broker      bought      sold
----------  ----------  ----------  ----------  ----------
1           X           A           20          0
1           X           B           0           10
1           X           C           0           10
1           Y           A           0           20
1           Y           B           0           0
1           Y           C           20          0
2           X           A           0           10
2           X           B           10          0
2           X           C           0           0
2           Y           A           0           10
2           Y           B           10          0
2           Y           C           0           0

The list of stocks and broker names are not fixed, i.e. they must be retrieved from the table itself (and thus cannot be "hardcoded" in the sql select statement).

Is this possible in a single (SQLite) SQL statement?

nmaier · Accepted Answer · 2013-08-18 15:14:54Z

I fell a bit like cheating using two subqueries and an union, but here you go.

SELECT date, stock, broker, SUM(bought) bought, SUM(sold) sold FROM (
  SELECT date, stock, buyer broker, SUM(quantity) bought, 0 sold FROM trades GROUP BY date, stock, buyer
  UNION
  SELECT date, stock, seller broker, 0 as bought, SUM(quantity) FROM trades GROUP BY date, stock, seller
) GROUP BY date, stock, broker;

Result:

date        stock       broker      bought      sold      
----------  ----------  ----------  ----------  ----------
1           X           A           20          0         
1           X           B           0           10        
1           X           C           0           10        
1           Y           A           0           20        
1           Y           C           20          0         
2           X           A           0           10        
2           X           B           10          0         
2           Y           A           0           10        
2           Y           B           10          0         
2           Z           D           11          0         
2           Z           E           0           11

Edit: I just saw you want to list really everything incl. all brokers on all days even if they did not make any trades on a/any stock(s). So here is the full summary:

SELECT date, stock, broker, SUM(bought) bought, SUM(sold) sold FROM (
  SELECT date, stock, buyer broker, SUM(quantity) bought, 0 sold FROM trades GROUP BY date, stock, buyer
  UNION
  SELECT date, stock, seller broker, 0 as bought, SUM(quantity) FROM trades GROUP BY date, stock, seller
  UNION
  SELECT DISTINCT d.date, s.stock, b.broker, 0 bought, 0 sold FROM (SELECT DISTINCT date FROM trades) d, (SELECT DISTINCT stock FROM trades) s, (SELECT DISTINCT seller broker FROM trades UNION SELECT DISTINCT buyer broker FROM trades) b
) GROUP BY date, stock, broker;

Result:

date        stock       broker      bought      sold      
----------  ----------  ----------  ----------  ----------
1           X           A           20          0         
1           X           B           0           10        
1           X           C           0           10        
1           X           D           0           0         
1           X           E           0           0         
1           Y           A           0           20        
1           Y           B           0           0         
1           Y           C           20          0         
1           Y           D           0           0         
1           Y           E           0           0         
1           Z           A           0           0         
1           Z           B           0           0         
1           Z           C           0           0         
1           Z           D           0           0         
1           Z           E           0           0         
2           X           A           0           10        
2           X           B           10          0         
2           X           C           0           0         
2           X           D           0           0         
2           X           E           0           0         
2           Y           A           0           10        
2           Y           B           10          0         
2           Y           C           0           0         
2           Y           D           0           0         
2           Y           E           0           0         
2           Z           A           0           0         
2           Z           B           0           0         
2           Z           C           0           0         
2           Z           D           11          0         
2           Z           E           0           11

Let's look at the queries:

SELECT date, stock, buyer broker, SUM(quantity) bought, 0 sold
FROM trades
GROUP BY date, stock, buyer

List only the sum of buys per (group by) date,stock,buyer. We need to have a sold column as well so that the UNIONs will work, so just make one with constant value 0. Works the same with sells.

SELECT DISTINCT d.date, s.stock, b.broker, 0 bought, 0 sold
FROM
 (SELECT DISTINCT date FROM trades) d,
 (SELECT DISTINCT stock FROM trades) s,
 (SELECT DISTINCT seller broker FROM trades
   UNION SELECT DISTINCT buyer broker FROM trades) b

This one is nasty. It will join (selected in the subqueries) all dates, stocks and brokers together and assigns them no bought and no sold. This is just there to get the full table, i.e. these rows are just fillers.

SELECT date, stock, broker, SUM(bought) bought, SUM(sold) sold
FROM ( ... )
GROUP BY date, stock, broker;

The sub queries will generate a lot of rows. This top query just aggregates the data.

ntlam · Accepted Answer · 2013-08-18 14:17:25Z

0

I think you can add some columns to your current table, calculating summary and fill in them each day. Then you can use SELECT statement.

answered Aug 18, 2013 at 14:17

ntlam

3131 gold badge2 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL select statement (SQLite)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related