OraFAQ Forum: SQL & PL/SQL » Nested Select for summary

Home » SQL & PL/SQL » SQL & PL/SQL » Nested Select for summary (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Nested Select for summary [message #611155]

Fri, 28 March 2014 08:35

andysam23586
Messages: 17
Registered: September 2009

Junior Member

Hi, I have a query which is working fine.
The resulting columns are: Marval Ref, Priority, Description, Breached, Date Occurred, Status, Last Updated, Updated By.

I now need to run a query that gets a summary of information.
The results I need should be
Priority, Total, Breached, %
There are 4 priorities 1, 2, 3 & 4
So the results will be Total number of Priority 1, and out of this total how many breached. Then the same for Priority 2, 3 & 4
The percentage column will be percentage of total breached.

I hope this makes sense.
My code so far is as follows

SELECT
  LCC_TPROBLEM.PNUMBER AS "Marval Ref",
  LCC_TPROBLEM.PPRIORITY AS "Priority",
  LCC_TPROBLEM.PDESCRIPT AS"Description",
  CASE WHEN (LCC_TPROBLEM.PEXFIX = '1')
  THEN 'Yes'
  ELSE 'NO'
  END as Breached,
  
  
 
  To_Date('01/01/1970','DD/MM/YYYY')+(LCC_TPROBLEM.POCCURRED/86400) AS"Date Occurred",
  LCC_TPROBLEM.PSTATUS AS "Status",
  To_Date('01/01/1970','DD/MM/YYYY')+(LCC_TPROBLEM.PMODIFIED/86400) AS "Last Updated",
  LCC_TPROBLEM.PMODIBY AS "Updated By"
  
FROM
  LCC_TPROBLEM,
  LCC_TPROBLEM  MEASURES,
  PURSUIT_TDICT2
WHERE
  ( LCC_TPROBLEM.PNUMBER=MEASURES.PNUMBER  )
  AND  ( PURSUIT_TDICT2.DICTSNO(+)=LCC_TPROBLEM.PDICT2  )
  AND  
  (
   case when LCC_TPROBLEM.PCALLTYPE = 'CHG' Then 'Change'  when LCC_TPROBLEM.PCALLTYPE = 'INC' then 'Incident' when LCC_TPROBLEM.PCALLTYPE = 'PRB' then 'Problem' when LCC_TPROBLEM.PCALLTYPE = 'TSK' then 'Task' else 'Unknown' end  In  ( 'Problem'  )
   AND
   LCC_TPROBLEM.PSTATUS  Not In  ( 'COMPLETE','CLOSED','SOLVED'  )
  )
GROUP BY
  LCC_TPROBLEM.PNUMBER, 
  LCC_TPROBLEM.PPRIORITY, 
  LCC_TPROBLEM.PDESCRIPT, 
  CASE WHEN (LCC_TPROBLEM.PEXFIX = '1')
  THEN 'Yes'
  ELSE 'NO'
  END,
   
  PURSUIT_TDICT2.DICTKEYDES, 
  To_Date('01/01/1970','DD/MM/YYYY')+(LCC_TPROBLEM.POCCURRED/86400), 
  LCC_TPROBLEM.PSTATUS, 
  To_Date('01/01/1970','DD/MM/YYYY')+(LCC_TPROBLEM.PMODIFIED/86400) , 
  LCC_TPROBLEM.PMODIBY, 
  To_Date('01/01/1970','DD/MM/YYYY')+(LCC_TPROBLEM.PNHMOD/86400)

Many thanks

Andy

Report message to a moderator