0

Trying to create a portfolio by selecting all transactions of given stock symbol, summing the shares (negative for "sell" actions) and then displaying only positions where the user_id fits && the total share count is above 0, however I get an error message when I try to add "AND shares > 0" to the WHERE section.

This is the error: RuntimeError: misuse of aggregate: SUM() [SQL: 'SELECT symbol, SUM(shares) as total_shares, price FROM transactions WHERE total_shares > 0 AND user_id = 1 GROUP BY symbol'] (Background on this error at: http://sqlalche.me/e/e3q8)

portfolio = db.execute("SELECT symbol, SUM(shares) AS shares, price FROM transactions WHERE user_id = :user_id AND shares > 0 GROUP BY symbol", user_id=session["user_id"])
Improve this question

1 Answer 1

Reset to default
3

You can't use the aggregated value in WHERE. WHERE is applied to the table data, and the aggregated values are created later.

There is the complementary HAVING clause, processed after aggregation. Use that instead. It's placed directly after the GROUP BY part.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .