Home » SQL & PL/SQL » SQL & PL/SQL » SQL result divided by another SQL result (merged 3)
SQL result divided by another SQL result (merged 3) [message #421541] Wed, 09 September 2009 02:38 Go to next message
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 [message #421546 is a reply to message #421541] Wed, 09 September 2009 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it in the lone second query with:
100*count(decode(PLOGDBY,PSOLVBY,1))/count(*)

Regards
Michel

[Updated on: Wed, 09 September 2009 02:44]

Report message to a moderator

Re: SQL result divided by another SQL result (merged 3) [message #421547 is a reply to message #421541] Wed, 09 September 2009 02:57 Go to previous messageGo to next message
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 #421551 is a reply to message #421547] Wed, 09 September 2009 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try it with SQL*Plus.

Regards
Michel
Re: SQL result divided by another SQL result (merged 3) [message #421552 is a reply to message #421541] Wed, 09 September 2009 03:41 Go to previous message
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
Previous Topic: Problem in Bursting Engine, Oracle R12
Next Topic: Issue with SELECT query of CURSOR
Goto Forum:
  


Current Time: Mon Sep 26 16:11:06 CDT 2016

Total time taken to generate the page: 0.10952 seconds