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

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

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

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

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

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

Richard


Interesting - I'll have a play tomorrow.

Roy Lambert

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

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

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