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
– BarcodePiglet, Commented Apr 27, 2015 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
– BarcodePiglet, Commented Apr 27, 2015 at 22:15
It does show TotalAmount column for every row, showing 159703 though. — BarcodePiglet
– BarcodePiglet, Commented Apr 27, 2015 at 22:15

Hart CO · Accepted Answer · 2015-04-27 22:35:35Z

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. :)
@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.

Community · Accepted Answer · 2017-05-23 12:24:22Z

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

Collectives™ on Stack Overflow

How to Increment Total Sum/Count in Row SQL

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related