CODEWITHSUNDEEP
sqlsqlite
Aman Kataria
Aman Kataria

Reputation: 616

(SQLITE) SUM based on cumulative range

I have a table like this:

id | sales |  profit | place
_____________________|______ 
1  |   2   |    1    |  US
2  |   3   |    -    |  SL
3  |   1   |    1    | India
4  |   0   |    -    |  Aus
5  |   2   |    -    |  -
6  |   4   |    1    |  UK
7  |   1   |    -    |  -

Now what I want to achieve is, wherever profit = 1, I want cumulative sales till that point in order of id column and the corresponding place i.e.

   |  cumulativeSales | place  |
   | ________________ |_______ |
   |     2            |  US    |      //(2)
   |     6            | India  |      //(2+3+1)
   |     12           |  UK    |      //(2+3+1+0+2+4)

What query should I write for this?

Upvotes: 0

Views: 1300

Answers (1)

Shawn
Shawn

Reputation: 52539

If using a modern version of sqlite (3.25 or newer), you can use window functions:

SELECT cumulativeSales, place
FROM (SELECT id, place, profit
           , sum(sales) OVER (ORDER BY id) AS cumulativeSales
      FROM yourtable)
WHERE profit = 1
ORDER BY id;

gives

cumulativeSales  place
---------------  ----------
2                US
6                India
12               UK

The window function form of sum() (Indicated by the following OVER clause) used in the inner query sums up a given window of the result rows. The default behavior with just an ORDER BY (Without an explicit framing term) is to use the first row up to all the rows with the same value that's being sorted on, but nothing greater. In other words, it calculates the cumulative sum. For way more detail, see the documentation.

The outer query just limits the results to those rows where profit is 1. If you did that all in one without the subquery, it'd only calculate the cumulative sum of those rows, not all the rows, because window functions are computed after WHERE filtering is done.

A different approach that uses a correlated subquery to calculate the running total, that works on older versions without window function support:

SELECT (SELECT sum(sales) FROM yourtable AS t2 WHERE t2.id <= t.id) AS cumulativeSales
     , place
FROM yourtable AS t
WHERE profit = 1
ORDER BY id;

Upvotes: 3

Related Questions