Using
an aggregate function in SQL is pretty straightforward. However we
sometimes need to have other columns in a table along with the corresponding
aggregate we are trying to calculate. This is not possible if we use the
functions in the traditional way using group by clause.
Lets take an example. Below is the table BookSales.
| BookID | DateOfSale | SaleCnt |
| 1001 | 2016-01-01 | 33 |
| 1001 | 2016-01-02 | 10 |
| 1001 | 2016-01-03 | 16 |
| 1002 | 2016-01-04 | 22 |
| 1001 | 2016-01-05 | 12 |
| 1001 | 2016-01-06 | 20 |
| 1002 | 2016-01-07 | 10 |
| 1001 | 2016-01-08 | 23 |
| 1002 | 2016-01-09 | 12 |
| 1002 | 2016-01-10 | 50 |
| 1001 | 2016-01-12 | 14 |
| 1001 | 2016-01-13 | 24 |
What I want to find is the running total for each book along with the dates. The result I am expecting is something like this
| BookID | DateOfSale | SaleCnt | RunningTot |
| 1001 | 2016-01-01 | 33 | 33 |
| 1001 | 2016-01-02 | 10 | 43 |
| 1001 | 2016-01-03 | 16 | 59 |
| 1001 | 2016-01-05 | 12 | 71 |
| 1001 | 2016-01-06 | 20 | 91 |
| 1001 | 2016-01-08 | 23 | 114 |
| 1001 | 2016-01-12 | 14 | 128 |
| 1001 | 2016-01-13 | 24 | 152 |
| 1002 | 2016-01-04 | 22 | 22 |
| 1002 | 2016-01-07 | 10 | 32 |
| 1002 | 2016-01-09 | 12 | 44 |
| 1002 | 2016-01-10 | 50 | 94 |
Query
SELECT A*,
SUM(SALECNT) OVER(PARTITION BY BOOKID
ORDER BY DATEOFSALE) AS RUNNINGTOT
FROM BOOKSALES A;
You could have the same result even if we change the display order using OrderBy clause in the query.
For Example
SELECT A*,
SUM(SALECNT) OVER(PARTITION BY BOOKID
ORDER BY DATEOFSALE) AS RUNNINGTOT
FROM BOOKSALES A
ORDER BY BOOKID, DATEOFSALE DESC;
---------------------------- RESULTS IN ------------------------------
| BookID | DateOfSale | SaleCnt | RunningTot |
| 1001 | 2016-01-13 | 24 | 152 |
| 1001 | 2016-01-12 | 14 | 128 |
| 1001 | 2016-01-08 | 23 | 114 |
| 1001 | 2016-01-06 | 20 | 91 |
| 1001 | 2016-01-05 | 12 | 71 |
| 1001 | 2016-01-03 | 16 | 59 |
| 1001 | 2016-01-02 | 10 | 43 |
| 1001 | 2016-01-01 | 33 | 33 |
| 1002 | 2016-01-10 | 50 | 94 |
| 1002 | 2016-01-09 | 12 | 44 |
| 1002 | 2016-01-07 | 10 | 32 |
| 1002 | 2016-01-04 | 22 | 22 |
No comments:
Post a Comment