How to Increment Total Sum/Count in Row SQL

Question

I am having trouble counting the TotalAmount incrementing by however many more number of policies there are iterating through each row.

For Example consider the following code:

SELECT 
  Customer.custno, 
  Customer.enteredDate AS 'Date Entered', 
  COUNT(BasicPolInfo.polid) AS 'Number of Policies', 
  SUM( COUNT(BasicPolInfo.polid)) over() AS TotalAmount    
FROM Customer 
INNER JOIN BasicPolInfo ON Customer.custid = BasicPolInfo.custid    
WHERE BasicPolInfo.polid IS NOT NULL 
  and Customer.firstname IS NOT NULL 
  AND Customer.enteredDate > '1/1/79'    
GROUP BY Customer.custno, Customer.firstname, Customer.lastname, Customer.entereddate    
ORDER BY Customer.enteredDate ASC

What I would like to see is the TotalAmount Column be added from the Number of Policies iterating through each and every customer.

ex:

21 -- date -- 6 -- 6
24 -- date -- 13 -- 19
25 -- date -- 23 -- 32
29 -- date -- 16 -- 48

I could care less for the order of the custno, rather I am more concerned if the total policies are even 159703? There are more than 1000 rows in this SQL.

Please help me how I am able to sum each row from the preceding total sum!

Sorry, this is actually my first post on Stack Overflow. SQL Server Management Studio 2014 — BarcodePiglet, Apr 27 '15 at 22:14
I was actually trying to attach a picture to make this question seem more sense, but unfortunately I am short points. Trying to get up there and hopefully eventually get this question answered. — BarcodePiglet, Apr 27 '15 at 22:15
It does show TotalAmount column for every row, showing 159703 though. — BarcodePiglet, Apr 27 '15 at 22:15

score 2 · Accepted Answer · answered Apr 27 '15 at 22:35

In SQL Server 2012 forward you can use ROWS in an analytic/window function to get a running aggregate:

SELECT  Customer.custno
      , Customer.enteredDate AS 'Date Entered'
      , COUNT(BasicPolInfo.polid) AS 'Number of Policies'
      , SUM(COUNT(BasicPolInfo.polid)) OVER (ORDER BY Customer.custno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS TotalAmount
FROM    Customer
        INNER JOIN BasicPolInfo ON Customer.custid = BasicPolInfo.custid
WHERE   BasicPolInfo.polid IS NOT NULL
        AND Customer.firstname IS NOT NULL
        AND Customer.enteredDate > '1/1/79'
GROUP BY Customer.custno
      , Customer.firstname
      , Customer.lastname
      , Customer.entereddate
ORDER BY Customer.enteredDate ASC

Note that while you don't care about the order, an ORDER BY is required in order to determine which rows precede the current row.

Har Co thanks so much. I am new to SQL server for my internship and am still learning. Would you mind explaining the 'OVER' function as well as what are in the parameters? Also, thanks for the syntax correction. Just changed mine to how you had set yours up! A lot easier to read. :) — BarcodePiglet, Apr 29 '15 at 16:08
@DanielKim My pleasure, `OVER()` basically defines a set/group of rows independently of the query's `GROUP BY`, some refer to it as a window. This allows you to return aggregates at different levels, there are many other uses and good examples out there. — Hart CO, Apr 29 '15 at 18:39

score 0 · Answer 2 · edited May 23 '17 at 12:24

It appears you are looking for a cumulative total.

This can be done via a CTE, joining the table on itself, a subquery or as of 2012 by using the "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" in the aggregate windowed function.

This can be done with any aggregated windowed function. You need to use

 OVER (ORDER BY ______ ORDER BY  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

*Note you need to use order by to specify the arrangement of the column.

The below link to another stack overflow question provides some clear examples.

how to get cumulative sum

How to Increment Total Sum/Count in Row SQL

2 Answers2