| SQL result divided by another SQL result (merged 3) [message #421541] |
Wed, 09 September 2009 02:38  |
andysam23586
Messages: 17
Registered: September 2009
|
Junior Member |
|
|
Hi
I have two queries that produce two results.
The first query is:
SELECT PLOGDBY, COUNT(PLOGDBY) AS "Calls Logged"
FROM LCC_TPROBLEM
WHERE ((PLOGDBY = 'WOODWARD_A' AND PSOLVBY = 'WOODWARD_A') OR
(PLOGDBY = 'GOSAI_P' AND PSOLVBY = 'GOSAI_P') OR
(PLOGDBY = 'BASSETT_A' AND PSOLVBY = 'BASSETT_A') OR
(PLOGDBY = 'MISTRY_R' AND PSOLVBY = 'MISTRY_R') OR
(PLOGDBY = 'MEADOWS_K' AND PSOLVBY = 'MEADOWS_K') OR
(PLOGDBY = 'SKINNER_R' AND PSOLVBY = 'SKINNER_R') OR
(PLOGDBY = 'DAVIS_Z' AND PSOLVBY = 'DAVIS_Z') OR
(PLOGDBY = 'BLOUNT_L' AND PSOLVBY = 'BLOUNT_L') OR
(PLOGDBY = 'PATEL_IS' AND PSOLVBY = 'PATEL_IS') OR
(PLOGDBY = 'THANDI_D' AND PSOLVBY = 'THANDI_D') OR
(PLOGDBY = 'FOOT_R' AND PSOLVBY = 'FOOT_R'))AND
(PENTERED BETWEEN 1246406400 AND 1249084799)
GROUP BY PLOGDBY
ORDER BY PLOGDBY
The results from this query are:
WOODWARD_A 283
THANDI_D 571
SKINNER_R 346
PATEL_IS 176
MISTRY_R 202
MEADOWS_K 94
GOSAI_P 60
FOOT_R 223
DAVIS_Z 143
BLOUNT_L 364
BASSETT_A 271
The second query is:
SELECT PLOGDBY, COUNT(PLOGDBY) AS "Calls Logged"
FROM LCC_TPROBLEM
WHERE ((PLOGDBY = 'WOODWARD_A') OR
(PLOGDBY = 'GOSAI_P') OR
(PLOGDBY = 'BASSETT_A') OR
(PLOGDBY = 'MISTRY_R') OR (
PLOGDBY = 'MEADOWS_K') OR
(PLOGDBY = 'SKINNER_R') OR
(PLOGDBY = 'DAVIS_Z') OR
(PLOGDBY = 'BLOUNT_L') OR
(PLOGDBY = 'PATEL_IS') OR
(PLOGDBY = 'THANDI_D') OR
(PLOGDBY = 'FOOT_R'))AND
(PENTERED BETWEEN 1246406400 AND 1249084799)
GROUP BY PLOGDBY
ORDER BY PLOGDBY
The reults of this query are:
WOODWARD_A 365
THANDI_D 898
SKINNER_R 483
PATEL_IS 236
MISTRY_R 281
MEADOWS_K 147
GOSAI_P 76
FOOT_R 283
DAVIS_Z 208
BLOUNT_L 516
BASSETT_A 357
What I need is a query to give the result of the first query divided by the second query multiplied by 100.
The results would be:
WOODWARD_A 77.53
THANDI_D 63.58
SKINNER_R 71.63
PATEL_IS 74.57
MISTRY_R 71.88
MEADOWS_K 63.94
GOSAI_P 78.94
FOOT_R 78.79
DAVIS_Z 68.75
BLOUNT_L 70.54
BASSETT_A 75.91
I believe I need to use a sub query but not sure. Hope someone can help.
|
|
|
|
|
|
| Re: SQL result divided by another SQL result (merged 3) [message #421547 is a reply to message #421541] |
Wed, 09 September 2009 02:57  |
andysam23586
Messages: 17
Registered: September 2009
|
Junior Member |
|
|
Thanks for the prompt reply Michel
I tried your suggestion like this:
SELECT PLOGDBY, 100*COUNT(decode(PLOGDBY,PSOLVBY,1))/count(*) AS "Calls Logged"
FROM LCC_TPROBLEM
WHERE ((PLOGDBY = 'WOODWARD_A') OR
(PLOGDBY = 'GOSAI_P') OR
(PLOGDBY = 'BASSETT_A') OR
(PLOGDBY = 'MISTRY_R') OR (
PLOGDBY = 'MEADOWS_K') OR
(PLOGDBY = 'SKINNER_R') OR
(PLOGDBY = 'DAVIS_Z') OR
(PLOGDBY = 'BLOUNT_L') OR
(PLOGDBY = 'PATEL_IS') OR
(PLOGDBY = 'THANDI_D') OR
(PLOGDBY = 'FOOT_R'))AND
(PENTERED BETWEEN 1246406400 AND 1249084799)
GROUP BY PLOGDBY
ORDER BY PLOGDBY
But got this error:
OCI-22053: overflow error
Any suggestions?
|
|
|
|
|
|
| Re: SQL result divided by another SQL result (merged 3) [message #421552 is a reply to message #421541] |
Wed, 09 September 2009 03:41 |
andysam23586
Messages: 17
Registered: September 2009
|
Junior Member |
|
|
Hi Michel
I have solved the problem by surrounding the Select in a TRUNC() like this:
SELECT PLOGDBY, TRUNC(100*COUNT(decode(PLOGDBY,PSOLVBY,1))/count(*)) AS "Calls Logged"
FROM LCC_TPROBLEM
WHERE ((PLOGDBY = 'WOODWARD_A') OR
(PLOGDBY = 'GOSAI_P') OR
(PLOGDBY = 'BASSETT_A') OR
(PLOGDBY = 'MISTRY_R') OR (
PLOGDBY = 'MEADOWS_K') OR
(PLOGDBY = 'SKINNER_R') OR
(PLOGDBY = 'DAVIS_Z') OR
(PLOGDBY = 'BLOUNT_L') OR
(PLOGDBY = 'PATEL_IS') OR
(PLOGDBY = 'THANDI_D') OR
(PLOGDBY = 'FOOT_R'))AND
(PENTERED BETWEEN 1246406400 AND 1249084799)
GROUP BY PLOGDBY
ORDER BY PLOGDBY
After googling the error it seems this is because it is returning a decimal and I am querying from .NET application.
Thank you very much for your help on this problem. Very much appreciated
Regards
Andy
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
