Home » SQL & PL/SQL » SQL & PL/SQL » Calculate One Query against another
Calculate One Query against another [message #438387] Fri, 08 January 2010 09:45 Go to next message
andysam23586
Messages: 17
Registered: September 2009
Junior Member
Hi,

I have two queries that work fine.
The first is
SELECT PASSGRP, COUNT(PASSGRP) AS Total
FROM LCC_TPROBLEM

WHERE (PASSGRP = 'SERVICE DESK 1ST LINE') AND (PCALLTYPE = 'INC') AND ((pstatus = 'SOLVED') OR (pstatus = 'COMPLETE') 
OR (PSTATUS = 'CLOSED')) AND (psolvat BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
                        AND ((TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))-TO_DATE('01-JAN-1970','DD-MON-YYYY'))+1)*(86400)-1)

GROUP BY PASSGRP


This gives me the result:
SERVICE DESK 1ST LINE 2649

My second query is:
SELECT PASSGRP, COUNT(PASSGRP) AS TotalRemote
FROM LCC_TPROBLEM

WHERE (PASSGRP = 'SERVICE DESK 1ST LINE') AND (PCALLTYPE = 'INC') AND (PDICT3 <> 106) AND (PDICT3 <> 118)AND (PDICT3 <> 0) AND ((pstatus = 'SOLVED') OR (pstatus = 'COMPLETE') 
OR (PSTATUS = 'CLOSED')) AND (psolvat BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
                        AND ((TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))-TO_DATE('01-JAN-1970','DD-MON-YYYY'))+1)*(86400)-1)

GROUP BY PASSGRP


This gives me the result:
SERVICE DESK 1ST LINE 511

What I need is a query that gives me the result of Query 2 (511)as a percentage of the result of Query 1 (2649).
E.G. 511/2649*100 = 19.29%
New query result would be:
SERVICE DESK 1ST LINE 19.29%

Hope this all makes sense.
Can anybody help.

Andy
Re: Calculate One Query against another [message #438389 is a reply to message #438387] Fri, 08 January 2010 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select cnt1, cnt2, 100*cnt1/cnt2 pct
  2  from (select count(distinct deptno) cnt1 from emp),
  3       (select count(*) cnt2 from dept)
  4  /
      CNT1       CNT2        PCT
---------- ---------- ----------
         3          4         75

1 row selected.

Regards
Michel
Re: Calculate One Query against another [message #438390 is a reply to message #438387] Fri, 08 January 2010 09:49 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Make each query an inline view:
SELECT PASSGRP, TotalRemote/Total
FROM (<query1>) q1,
(<query2>) q2
WHERE q1.passgrp = q2.passgrp


There are probably more efficient ways of doing it with analytics mind.
Re: Calculate One Query against another [message #438393 is a reply to message #438387] Fri, 08 January 2010 10:18 Go to previous message
andysam23586
Messages: 17
Registered: September 2009
Junior Member
Thank you both the query works fine.
Much appreciated

Andy
Previous Topic: Format Excel Using PLSQL
Next Topic: Problem in creating procedure
Goto Forum:
  


Current Time: Tue Sep 27 00:44:59 CDT 2016

Total time taken to generate the page: 0.05910 seconds