Greetings,

Could someone explain how RUNSUM works? Let's say that I wanted to count the number of tests in each of the following groups.


CREATE TABLE "PathologyTests"
(
"PathologyGroup" VARCHAR(24) COLLATE "ANSI_CI" NOT NULL,
"PathologyTest" VARCHAR(24) COLLATE "ANSI_CI" NOT NULL,
CONSTRAINT "prPrimaryIND" PRIMARY KEY ("PathologyGroup", "PathologyTest")
)

"PathologyGroup"|"PathologyTest"
"FBC"|"RBC"
"FBC"|"WBC"
"LFT"|"ALT"
"AST"|"AST"
"LFT"|"LDH"
"Lipids"|"Cholesterol"
"Lipids"|"HDL Cholesterol"
"Lipids"|"LDL Cholesterol"
"Lipids"|"Triglycerides"


CREATE VIEW "PathologyRunSum" AS
SELECT
    PathologyGroup, RunSum(1) AS TestCount
 FROM PathologyTests
 GROUP BY PathologyGroup

RUNSUM produces the following results.

"PathologyGroup"|"TestCount'
"FBC"|2
"LFT"|5
"Lipids"|9

I thought that it should produce this instead.

"PathologyGroup"|"TestCount"
"FBC"|2
"LFT"|3
"Lipids"|4

Thank you
Richard

Hi Richard:

SELECT
    PathologyGroup, Count(*) AS TestCount
 FROM PathologyTests
 GROUP BY PathologyGroup

"PathologyGroup"|"TestCount"
"FBC"|2
"LFT"|3
"Lipids"|4

You need COUNT to get what you are looking for .... RunSum does as you saw a Running Sum  (continuous).

Walter

Greetings Walter

Thank you for your response.

There has been several posts in the past regarding people using RUNSUM as a simple way to provide a sequence numbers for a dataset. This can be used to provide rankings. Although this can produce problems if Fred and Mary have the same test score but Fred gets a higher ranking. Mary is entitled to feel disappointed.

I wanted to get an understanding of how RUNSUM works. I thought RUNSUM should be able to "reset" the start of the sequence number with the change of PATHOLOGYGROUP. Is there a problem with RUNSUM or is there a problem with my understanding of how RUNSUM works?


CREATE VIEW "PathologyRunSum" AS
SELECT
   PathologyGroup, RunSum(1) AS TestCount
FROM PathologyTests
GROUP BY PathologyGroup

RUNSUM produces the following results.

"PathologyGroup"|"TestCount"
"FBC"|2
"LFT"|5
"Lipids"|9

I thought that it should produce this instead.

"PathologyGroup"|"TestCount"
"FBC"|2
"LFT"|3
"Lipids"|4


As a separate exercise, occasionally I wish to provide RANKINGs for datasets. For those interested, the following example of an alternative way to provide rankings that take into account duplicate results. The example is an artificial example but gives an indication of how it can be done.

CREATE TABLE "PathologyResults"
(
"Name" VARCHAR(24) COLLATE "ANSI_CI" NOT NULL,
"Test" VARCHAR(24) COLLATE "ANSI_CI",
"TestResult" DECIMAL(19,2) NOT NULL
)

CREATE VIEW "PathologyResultsRanking" AS
SELECT
   P1.Name, P1.Test, P1.TestResult, COUNT(P1.TestResult) AS ResultRank
 FROM
   PathologyResults AS P1
     INNER JOIN PathologyResults AS P2 ON
        P1.Test=P2.Test
 WHERE (P1.TestResult <= P2.TestResult) OR
        (P1.Name = P2.Name)
 GROUP BY P1.Test, P1.Name, P1.TestResult
 ORDER BY P1.TestResult DESC, P1.Test DESC, P1.Name


"Name"|"Test"|"TestResult"|"ResultRank"
"Titania"|"CHOL"|5.2|1
"Desdemona"|"CHOL"|4.8|3
"Juliet"|"CHOL"|4.8|3
"Miranda"|"CHOL"|4.3|4
"Rosalind"|"CHOL"|4.2|5
"Oberon"|"CHOL"|4|6
"Titania"|"Triglyceride"|1.7|1
"Oberon"|"Triglyceride"|1.6|2
"Juliet"|"Triglyceride"|1.5|3
"Desdemona"|"Triglyceride"|1.4|5
"Rosalind"|"Triglyceride"|1.4|5
"Miranda"|"Triglyceride"|1.2|6

Richard

Richard


<<Is there a problem with RUNSUM or is there a problem with my understanding of how RUNSUM works?>>

I'm afraid its the second. RUNSUM is doing exactly what it says on the tin. From the (older version) pdf manual
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RUNSUM Function
The RUNSUM function calculates the sum of values for a column in a running total. The syntax is as follows:
RUNSUM(column_reference or expression)

Use RUNSUM to sum all the values in the specified column in a continuous running total. The RUNSUM function is
identical to the SUM function except for the fact that it does not reset itself when sub-totalling.

Note
The running total is only calculated according to the implicit order of the GROUP BY fields and is not affected by
an ORDER BY statement.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The critical bit is "continuous running total". As an experiment you could change RUNSUM(1) to SUM(1) which will do what you want as well as COUNT(*)

Roy Lambert

Thank you Roy

<<Use RUNSUM to sum all the values in the specified column in a continuous running total. The RUNSUM function is identical to the SUM function except for the fact that it does not reset itself when sub-totalling.>>

That does explain the RUNSUM behaviour. What I do not understand is the exception - why it is necessary for RUNSUM to behave differently to SUM?

I do not need RUNSUM. The example provided shows how sequencing and ranking can be done but it is more complicated. I thought RUNSUM may be a shortcut to perform sequencing and ranking operations in a limited set of circumstances.

RUNSUM does not appear to be implemented in any other DB so it is not possible to compare how it is implemented elsewhere.

I appreciate your explanation.

Richard

Richard

>That does explain the RUNSUM behaviour. What I do not understand is the exception - why it is necessary for RUNSUM to behave differently to SUM?

If it didn't behave differently what would be the point of it?

SUM gives you a single value for a group of rows (if the GROUP BY clause is ommited the group is all the rows), RUNSUM gives a running count and is different for each row.

Roy

On 11/22/2021 5:40 PM, Richard Harding wrote:
> That does explain the RUNSUM behaviour. What I do not understand is the exception - why it is necessary for RUNSUM to behave differently to SUM?

Sometimes one wants running total in addition to sum in same resultset -
for example total sales by customer (sum) as well running total across
(runsum).

Something like this works easy in EDB:

select CustomerID,sum(PoTotal) as CustomerTotal, runsum(PoTotal) as
RunningTotal from Invoices group by CustomerID

> I do not need RUNSUM. The example provided shows how sequencing and ranking can be done but it is more complicated. I thought RUNSUM may be a shortcut to perform sequencing and ranking operations in a limited set of circumstances.

OK but others might find it useful


> RUNSUM does not appear to be implemented in any other DB so it is not possible to compare how it is implemented elsewhere.

Had to do something like this in older MS-SQL and it was PITA (subquery
doing SUM up to current point)

Newer MS-SQL versions support OVER and PARTITION BY to make this easier
but have not had to do much there so not really expert.

Raul