Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 11 total |
Calculation wierdness |
Fri, Oct 12 2018 4:55 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | I'm helping Ian out with a query - this one works SELECT JobType, SUM(Qty) AS "Total Units", COALESCE((SELECT SUM(X.Qty) FROM JobTickets X WHERE X.JobType = JobTickets.JobType AND X.JobCode = 'SVC' RANGE 1 TO 1),0) AS Bounces, CAST( 100* (SELECT COALESCE(SUM(P.Qty),0) FROM JobTickets P WHERE P.JobType = JobTickets.JobType AND P.JobCode = 'SVC' ) / (SELECT COALESCE(SUM(Qty),0) FROM JobTickets Q WHERE Q.JobType = JobTickets.JobType ) AS NUMERIC(5,2)) AS Pcnt FROM JobTickets GROUP BY JobType but if I change it to SELECT JobType, SUM(Qty) AS "Total Units", COALESCE((SELECT SUM(X.Qty) FROM JobTickets X WHERE X.JobType = JobTickets.JobType AND X.JobCode = 'SVC' RANGE 1 TO 1),0) AS Bounces, CAST( 100* (SELECT COALESCE(SUM(P.Qty),0) FROM JobTickets P WHERE P.JobType = JobTickets.JobType AND P.JobCode = 'SVC' ) / SUM(Qty) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< AS NUMERIC(5,2)) AS Pcnt FROM JobTickets GROUP BY JobType it doesn't. EG the first line of output is 1 0 0.00 but the second query returns 1 0 100.00 its as if with the second query the second SUM(Qry) ALWAYS returns 1 and I don't understand it. Roy Lambert |
Fri, Oct 12 2018 10:09 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Roy and Ian
Query 1 ----------- / (SELECT COALESCE(SUM(Qty),0) FROM JobTickets Q WHERE Q.JobType = JobTickets.JobType ) AS NUMERIC(5,2)) AS Pcnt <<<<<<<< Not the the sum of all QTYs in JobTickets FROM JobTickets GROUP BY JobType Query 2 ---------- / SUM(Qty) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< AS NUMERIC(5,2)) AS Pcnt FROM JobTickets <<< SUM(Qty) is the sum of all QTYs in JobTickets GROUP BY JobType ======================================= I assume that you wish to obtain the percentage of serviced items for each Job Type. CREATE TABLE "JobTickets" ( "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL, "JobType" INTEGER DEFAULT 0 NOT NULL, "JobCode" VARCHAR(3) COLLATE "ANSI_CI" DEFAULT 'DEF' NOT NULL, "Cost" DECIMAL(19,4) DEFAULT 0 NOT NULL, "Qty" INTEGER DEFAULT 1 NOT NULL ) SELECT JobType, Sum(Qty), (SELECT Sum(Qty) FROM JobTickets J2 WHERE J1.JobType = J2.JobType AND J2.JobCode = 'SVC') AS ServicedItems, COALESCE((SELECT Sum(Qty) FROM JobTickets J3 WHERE J1.JobType = J3.JobType AND J3.JobCode = 'SVC') / (SELECT Sum(Qty) FROM JobTickets J4 WHERE J1.JobType = J4.JobType), 0) AS ServicedPercent FROM JobTickets J1 GROUP BY JobType Richard |
Sat, Oct 13 2018 2:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
Thanks, but what you've done there is essentially duplicate the first query I posted which does work, What I'm interested in is why I need to use a sub-select and the second query doesn't work. Roy Lambert |
Sat, Oct 13 2018 8:50 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Roy
Query 1 ----------- (SELECT COALESCE(SUM(Qty),0) FROM JobTickets Q WHERE Q.JobType = JobTickets.JobType ) <<<<<<<< NOTE [1] AS NUMERIC(5,2)) AS Pcnt FROM JobTickets GROUP BY JobType Query 2 ---------- SUM(Qty) <<<<<<<<<<<<<<<<<<< NOTE [2] AS NUMERIC(5,2)) AS Pcnt FROM JobTickets GROUP BY JobType NOTE [2] SUM(Qty) is the sum of all Qty for the table JobTickets. NOTE [1] (SELECT COALESCE(SUM(Qty),0) FROM JobTickets Q WHERE Q.JobType = JobTickets.JobType ) is NOT the sum of all Qty for the table JobTickets. It has a WHERE condition which limits the result to the required JobType. Richard |
Sun, Oct 14 2018 4:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
>NOTE [2] SUM(Qty) is the sum of all Qty for the table JobTickets. This is why I don't understand. You've missed the GROUP BY implications SUM(Qty) is the sum of Qty for the rows in the JobTicket group and is correct as shown by SUM(Qty) AS "Total Units", >NOTE [1] (SELECT COALESCE(SUM(Qty),0) FROM JobTickets Q WHERE Q.JobType = JobTickets.JobType ) is NOT the sum of all Qty for the table JobTickets. It has a WHERE condition which limits the result to the required JobType. The WHERE clause restricts the selection to the same as the GROUP BY clause hence SUM(Qty) and the subselect should return the same result - and they do unless used in the calculation. This is my puzzle. Roy |
Mon, Oct 15 2018 8:35 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Roy
Yes - you are right. I was completely confused with what was going on. Although it appears that the problem is with the numerator rather than the Sum(qty) in the denominator. Not that it helps very much although it might help Tim if you can confirm it. I used the following data. "ID","JobType","JobCode","Qty","Cost" 2,1,"DEF",2,5 1,1,"DEF",1,1 3,2,"SVC",2,2 4,2,"DEF",10,5 9,2,"DEF",8,1 10,3,"DEF",5,0 11,3,"DEF",1,0 12,4,"DEF",6,0 13,4,"SVC",4,0 14,5,"SVC",1,0 I obtained the following results. "JobType","Total Units","Bounced","PCnt" 1,3,,0.333333333333333 2,20,2,0.05 3,6,,0.166666666666667 4,10,4,0.1 5,1,1,1 PCnt is obtained from dividing number of "Bounced" items in the 5th row (that is, 1) with the "Total Units" in each row. What do you think? Richard |
Mon, Oct 15 2018 10:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
Interesting - I'll have a play tomorrow. Roy Lambert |
Tue, Oct 16 2018 1:38 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Interesting - I'll have a play tomorrow. >> If you have something that you think is a bug, please send it over along with a database catalog/tables and I'll take a look. Tim Young Elevate Software www.elevatesoft.com |
Wed, Oct 17 2018 4:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>If you have something that you think is a bug, please send it over along with a database catalog/tables and I'll take a look. Sent to your email Roy |
Tue, Oct 23 2018 1:06 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< it doesn't. >> The issue is the CAST() with the second expression. If you remove it, then it will work. I'll have a fix for this in 2.29. Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |