Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 7 of 7 total |
RUNSUM |
Wed, Nov 17 2021 2:45 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Wed, Nov 17 2021 3:39 PM | Permanent Link |
Walter Matte Tactical Business Corporation | 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 |
Sat, Nov 20 2021 10:53 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Sun, Nov 21 2021 3:50 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Nov 22 2021 5:40 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Tue, Nov 23 2021 3:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Nov 23 2021 8:55 AM | Permanent Link |
Raul Team Elevate | 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 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |