Cummulative Total

Question

Post reply

Cummulative Total

jagjitsingh

Ten Centuries

Points: 1068
More actions
March 26, 2025 at 10:41 am

#4567625

Hi
I have below query and i want Cummulative Total . Total should be reset on change of Account
SELECT
ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "Row No",
A."Account" AS "Account",(A."Debit") AS "Debit", (A."Credit") AS "Credit"
From Master A
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Phil Parkin SSC Guru Points: 247131 More actions · Answer 1

Can you provide some consumable sample data?

If a single account has multiple rows, how are those rows to be ordered?

jagjitsingh Ten Centuries Points: 1068 More actions · Answer 2

Error - Windowed functions cannot be used in the context of another windowed function or aggregate.

SELECT ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "RowNo", A."Account" AS "Account", (A."Debit") AS "Debit", SUM(A."Debit" - A."Credit") OVER (PARTITION BY Account ORDER BY ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account")) AS Balance From Master A

Thanks

Phil Parkin SSC Guru Points: 247131 More actions · Answer 3

Phil Parkin

SSC Guru

Points: 247131

March 26, 2025 at 12:17 pm

#4567691

Thanks for what? Did you understand my post?

Jonathan AC Roberts SSCoach Points: 18404 More actions · Answer 4

jagjitsingh wrote:

Error - Windowed functions cannot be used in the context of another windowed function or aggregate.
SELECT ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "RowNo", A."Account" AS "Account", (A."Debit") AS "Debit", SUM(A."Debit" - A."Credit") OVER (PARTITION BY Account ORDER BY ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account")) AS Balance From Master A
Thanks

WITH cte AS 
(
    SELECT ROW_NUMBER() OVER (PARTITION BY A."Account" ORDER BY A."Account") AS "RowNo",
           A."Account",
           A."Debit",
           A."Credit"
      FROM Master A
)
SELECT "RowNo",
       "Account",
       "Debit",
       SUM("Debit" - "Credit") OVER (PARTITION BY "Account" ORDER BY "RowNo") AS "Balance"
  FROM cte;

drew.allen SSC Guru Points: 77009 More actions · Answer 5

jagjitsingh wrote:

Error - Windowed functions cannot be used in the context of another windowed function or aggregate.
SELECT ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "RowNo", A."Account" AS "Account", (A."Debit") AS "Debit", SUM(A."Debit" - A."Credit") OVER (PARTITION BY Account ORDER BY ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account")) AS Balance From Master A
Thanks

There are several problems here.

Your ORDER BY clause is non-deterministic. You're ordering by Account within a group on Account, so all of the records in the group necessarily have the same value, so ANY order is valid. Furthermore, this order could change every time you reference that value EVEN WITHIN THE SAME QUERY.
You're using ROW_NUMBER() to set an order, but that order is in turn set by the ORDER BY clause in the OVER() clause. Therefore, the ROW_NUMBER() is superfluous. Simply use the original ORDER BY clause.
When using running totals, you should ALWAYS specify the window frame. The default is RANGE UNBOUNDED PRECEDING. Since you are using a partition field for your order by clause ALL of your records within the partition are within the range. This means that you are getting a grand total instead of a running total.

Here is how I would rewrite your query. NOTE: I have left open the fields that specify a unique or primary key to use in determining the order.

SELECT A.Account AS Account
     , (A.Debit) AS Debit
     , SUM(A.Debit - A.Credit) OVER (PARTITION BY A.Account ORDER BY <Your unique key fields here> ROWS UNBOUNDED PRECEDING) AS Balance
From Master A

On another note, you shouldn't be using double quotes around your field names. If you want to use anything, you should use open and close square brackets. "[" and "]".

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

pietlinden SSC Guru Points: 63557 More actions · Answer 6

Use SUM() instead of ROW_NUMBER()?

https://codingsight.com/calculating-running-total-with-over-clause-and-partition-by-clause-in-sql-server/

This reply was modified 2 weeks, 2 days ago by pietlinden.

kaj SSCommitted Points: 1615 More actions · Answer 7

drew.allen wrote:

On another note, you shouldn't be using double quotes around your field names. If you want to use anything, you should use open and close square brackets. "[" and "]".
Drew

Hmm... On what grounds do you advise against using double quotes and instead using the ugly square brackets? My understanding is that double quotes are quite okay, whilst single quotes aren't, since they are depricated. Or am I misinformed?

Phil Parkin SSC Guru Points: 247131 More actions · Answer 8

kaj wrote:

drew.allen wrote:
On another note, you shouldn't be using double quotes around your field names. If you want to use anything, you should use open and close square brackets. "[" and "]".
Drew
Hmm... On what grounds do you advise against using double quotes and instead using the ugly square brackets? My understanding is that double quotes are quite okay, whilst single quotes aren't, since they are depricated. Or am I misinformed?

It's Microsoft's standard to use square brackets for such things in T-SQL, despite it not being ANSI standard. And with square brackets, you don't run the risk of falling foul of the quoted identifier setting.

SET QUOTED_IDENTIFIER OFF;

SELECT TOP(10)
       "name"
      ,[name]
FROM sys.databases;

kaj SSCommitted Points: 1615 More actions · Answer 9

Phil Parkin wrote:

kaj wrote:
drew.allen wrote:
On another note, you shouldn't be using double quotes around your field names. If you want to use anything, you should use open and close square brackets. "[" and "]".
Drew
Hmm... On what grounds do you advise against using double quotes and instead using the ugly square brackets? My understanding is that double quotes are quite okay, whilst single quotes aren't, since they are depricated. Or am I misinformed?
It's Microsoft's standard to use square brackets for such things in T-SQL, despite it not being ANSI standard. And with square brackets, you don't run the risk of falling foul of the quoted identifier setting.
SET QUOTED_IDENTIFIER OFF;

SELECT TOP(10)
       "name"
      ,[name]
FROM sys.databases;

Thanks, makes sense. I don't usually use double quotes like the OP so hadn't thought about the SET QUOTED_IDENTIFIER setting in that regard. I strive to avoid having to use any of the quoting options, but occationally I do use double quotes as column alias if the output is used in for example an Excel spreadsheet where the column alias work as a column heading and need a more meaningful/readable name than the original column name. But I guess that's a different game.

SET QUOTED_IDENTIFIER OFF;

SELECT TOP(10)
       name as "Meaningful column name for use in Excel"
      ,[name]
FROM sys.databases as d;

This reply was modified 1 week, 4 days ago by kaj.

Cummulative Total

Cookies on SQLServerCentral