Trying to get running Total in SQL Server

Question

Trying to get running Total in SQL Server

devsql123

SSC-Addicted

Points: 478
More actions
June 2, 2015 at 1:34 am

#304559

Hi Folks,
I am looking for solution related to get Running Total in SQL Server.
I found that new OVER clause can help, as explained below.
http://blog.sqlauthority.com/2014/10/04/sql-server-how-to-find-running-total-in-sql-server/
SELECT
CompanyId
,IMNId
,RowNumber
,Sum(Qty) OVER(ORDER BY CompanyId, IMoneyNetId, RowNumber ROWS UNBOUNDED PRECEDING) As CumulativeShareQty2
From#ReportData
WhereSequenceNumber <> 3
Group by CompanyId
,IMNId
,RowNumber
But somehow i get below error, when i try to run above query:
Msg 8120, Level 16, State 1, Line 957
Column '#ReportData.Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
As per my thinking logic is correct, so please help me to clear this doubt.
Thanks
devsql

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

devsql123 (6/2/2015)
Hi Folks,
I am looking for solution related to get Running Total in SQL Server.
I found that new OVER clause can help, as explained below.
http://blog.sqlauthority.com/2014/10/04/sql-server-how-to-find-running-total-in-sql-server/
SELECT
CompanyId
,IMNId
,RowNumber
,Sum(Qty) OVER(ORDER BY CompanyId, IMoneyNetId, RowNumber
ROWS BETWEEN UNBOUNDED PRECEDING ) As CumulativeShareQty2
From#ReportData
WhereSequenceNumber <> 3
Group by CompanyId
,IMNId
,RowNumber
But somehow i get below error, when i try to run above query:
Msg 8120, Level 16, State 1, Line 957
Column '#ReportData.Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
As per my thinking logic is correct, so please help me to clear this doubt.
Thanks
devsql

Quick note, you cannot use the over clause with group by, the two are incompatible.

😎

Try this

SELECT

CompanyId

,IMNId

,RowNumber

,Sum(Qty) OVER(ORDER BY CompanyId, IMoneyNetId, RowNumber

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW) As CumulativeShareQty2

From#ReportData

WhereSequenceNumber <> 3;

devsql123 SSC-Addicted Points: 478 More actions · Answer 2

Hi Eirikur

1-

Thank you very much for showing me this solution.

2-

May i know, why you used/added "AND CURRENT ROW" clause ?

3-

when i try to run your query, i got below error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:

There is an error in XML document (1, 8201).

Instance validation error: 'Window Spool' is not a valid value for PhysicalOpType.

So what is this error...looks very much weired...!!!

4-

As per my understanding from below article,

When ROWS clause is specified with UNBOUNDED PRECEDING then result is that the window starts at the first row of the partition.

OVER Clause (Transact-SQL)

so based on above my query is perfect (i feel so...).

so please help me...

Thanks

devsql

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

My bad, I missed the partition by clause which effectively replaces the group by, this code should do the trick

😎

SELECT

CompanyId

,IMNId

,RowNumber

,Sum(Qty) OVER

(

PARTITION BY CompanyId

,IMNId

,RowNumber

ORDER BY CompanyId

,IMoneyNetId

,RowNumber

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) As CumulativeShareQty2

From#ReportData

WhereSequenceNumber <> 3;

Although the defaults for the over clause do imply "and current row", I prefer to spell them out rather than relying on the defaults.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Although the defaults for the over clause do imply "and current row", I prefer to spell them out rather than relying on the defaults.

One should ALWAYS explicitly fill in ALL defaults such as this. Help yourself AND the next person who has to interpret or debug your code!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

j-1064772 SSCertifiable Points: 5316 More actions · Answer 5

[font="Comic Sans MS"]The topic of Running Totals has been covered in excrutiating details on numerous occasions in this forum.

The most recent I can suggest (I started it last week) is at "http://www.sqlservercentral.com/Forums/Topic1689661-3077-1.aspx"

That thread is still alive (latest entry was yesterday), you can ask your questions there.

No need to respin the same wheel over and over again.

Regards[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

j-1064772 (6/3/2015)
No need to respin the same wheel over and over again.

]

BWAAA-HAAAAA!!!!! If that were true, there would be no use for forums at all because everything that can be answered has already been asked at least once. 😀

Let the wheels spin. Innovation and invention ride on the wheels of questions. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

j-1064772 SSCertifiable Points: 5316 More actions · Answer 7

Jeff Moden (6/4/2015)
j-1064772 (6/3/2015)
No need to respin the same wheel over and over again.
]
BWAAA-HAAAAA!!!!! If that were true, there would be no use for forums at all because everything that can be answered has already been asked at least once. 😀
Let the wheels spin. Innovation and invention ride on the wheels of questions. 🙂

[font="Comic Sans MS"]Touché.

I was more concerned with excess complexity which in the end makes finding things more difficult - sort of like having to deal with 100 search results instead of (wishful thinking) maybe a half-dozen.

Warning: RANT_MODE: ON

Warning: GROSS_ABUSE_OF_THE_FORUM_MODE: ON

A pet peeve was Crystal Reports' knowledge base - try finding anything in there.

Another one is Amazon's ham-fisted way of sorting textbooks:

1. by popularity - what does a 5-star overall rating mean when only two persons have issued a rating ? A four-star rating with more than half-dozen 1-star ratings - some of which are sometimes far more useful than 5-star ratings which do not consider the reviewers' level such as self-study, post doctoral researcher ...

2. by vendor (for used books: skip vendors with a poor rating such as those selling mildewed (biological hazard level) books as excellent because the jackets are still there - no way to refine the search for a specific category physics, maths, etc.

3. by price - without taking into account the condition of the book ignoring basic reality that a book in poor condition is definitely cheaper than good or very good and you have to open the link and then view the offerings, where the cheap ones' condition rates as "Dumpster material" - this ends up as a "bait-and-switch" if you want a clean book then the low price that got you to open the link no longer applies.

Amzon's customer service has ignored my suggestions to improve on this.

RANT_MODE: OFF

GROSS_ABUSE_OF_THE_FORUM_MODE: OFF

I feel so much better I got it out.[/font]