Search code examples
sqlitepython-3.7

How to sum row elements in extra column and find out data where some of field is > than x?

SELECT *FROM 'product'
row_id quantity
1 100
2 200
3 300
4 400

I would like to select x product, which sum of quantity is > than 100.

This is my expected output:

row_id quantity total
2 200 300
3 300 600
4 400 1000
Solution

  • Use SUM() window function:

    SELECT *
FROM (
  SELECT *, SUM(quantity) OVER (ORDER BY rowid) total
  FROM product
)
WHERE total > 100

    Foe versions of SQLite that do not support window functions use a correlated subquery:

    SELECT *
FROM (
  SELECT p.*, (SELECT SUM(pp.quantity) FROM product pp WHERE pp.rowid <= p.rowid) total
  FROM product p
)
WHERE total > 100

    See the demo.