SQLite How do I separate a table by column values?

Question

I'm writing a simple stock trading site with Flask while learning how to use SQLite database. When a logged in user makes a transaction (buy/sell), I insert it into the transactions database with this command:

db.execute("INSERT INTO transactions (user_id, symbol, shares, price) VALUES (:user_id, :symbol, :shares, :price)",
           user_id=session["user_id"], symbol=symbol, shares=shares, price=price)

If the user wants to check what stocks they currently have, I just query it from the database:

trades = db.execute("SELECT * FROM transactions WHERE user_id = :user_id ORDER BY transaction_id ASC",
                    user_id=session["user_id"])

But this gives me back all of the transactions for both buy and sell. The way I set it up is that when a user sells a stock, the shares column will be a negative number. At this point it became pretty messy for me to organize the data in my code. But I'm not sure if I should add another database since I will also have to insert and query that database separately in my code.

Is there a command that allows me to split buy and sell transactions into two separate tables?

Edit: Typo in command

can't you just make 2 database calls? – Anton vBR Sep 26 '18 at 19:21 — Anton vBR, Sep 26 '18 at 19:21

Johan · Accepted Answer · 2018-09-26T19:52:42.103

1

If you only want to show the buy orders you just need to filter the select query by fetching all rows where shares are positive

trades = db.execute("
    SELECT * FROM transactions 
    WHERE user_id = :user_id AND shares > 0 
    ORDER BY transaction_id ASC",
    user_id=session["user_id"]
)

However, if you want to show the total holdings for each stock/symbol, you can use a group by clause and sum the shares column.

holdings = db.execute("
    SELECT id, symbol, SUM(shares) 
    FROM transactions 
    WHERE user_id = :user_id
        AND SUM(shares) > 0 -- Edit: Added this so it will only show stocks you own
    GROUP BY symbol 
    ORDER BY transaction_id ASC",
    user_id=session["user_id"]
)

Sidenote: I believe that the id is wrong in your second select query and you mean user_id (in the WHERE id = :user_id part).

edited Sep 26 '18 at 19:52

answered Sep 26 '18 at 19:21

Johan

3,577
1
14
28

You're right. I was typing out my code in this question while looking at my IDE so I didn't catch that error. – Kingle Sep 26 '18 at 20:00
Thank you, this works perfectly. I was dreading having to create another database. – Kingle Sep 26 '18 at 20:01
No problem :) Glad to help – Johan Sep 26 '18 at 20:02

score 0 · Answer 2 · answered Sep 26 '18 at 19:22

0

You could run two queries insert into select .... See this SO question about how to do one.

You'd just have to run a first one for the sell transactions and another one for the buy transactions.

answered Sep 26 '18 at 19:22

Léopold Houdin

1,515
13
18

score 0 · Answer 3 · answered Sep 26 '18 at 19:24

0

Cant you just:

SELECT * FROM transactions WHERE id = :user_id and shares > 0 ORDER BY transaction_id ASC

to get shares they own, and then shares < 0 to get shares they've sold?

answered Sep 26 '18 at 19:24

Njord

142
6

SQLite How do I separate a table by column values?

3 Answers3