Solved

calculating averages - oracle 10 sql

Posted on 2010-11-24
3
299 Views
Last Modified: 2012-05-10
Calculating averages - oracle 10 sql

Please see the attached:
fig 1. Current output from o_complaints table
fig 2. Desired output

The data is looking at complaints made against organisations - it includes clients that have made the complaints, the organisations the complaints have been made against and the type of complaint

I want the output to provide aggregated information - listing each organisation once, the number of complaints made against the organisation and the percentage of complaints made against the organisation. In addition id like a count of the type of complaint made against the organisation and the type of complaint as a percentage.

So for example Organisation 9 - had 3 complaints (comp_count), which was 20% of the complaints (comp_perc), 1 or these complaints was about the product (prod) so this was 6.6% (prod_perc) of the total number of complaints

One other thing to note – if a complaint does not have a complaint type recorded (eg. The complaint made against organisation 8) the complaint type should be classed as ‘Not Specified).

Any help to achieve this output is appreciated.

example-table-2.xls
0
Comment
Question by:tonMachine100
3 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34206386
If no other experts give any help, i can help tomorrow on this. Going to bed now.
0
 
LVL 20

Accepted Solution

by:
flow01 earned 250 total points
ID: 34207927
I did add on organisation table and did round the results

with  org
as
(select 'Organisation ' || level comp_org
 from dual connect by level <= 10)
select comp_org,
(prod + serv + del + not_spec) prod_count, round((prod + serv + del + not_spec) * 100/tot,1)
prod, round(prod * 100/tot,1) prod_perc,
serv, round(serv * 100/tot,1) serv_perc,
del,  round(del  * 100/tot,1) del_perc,
not_spec, round(not_spec * 100/tot,1) not_spec_perc
from
(
select o.comp_org,
 (select count(*) from  o_complaints c
  where o.comp_org  = c.comp_org
  and c.comp_type  = 'Product') prod,
 (select count(*) from  o_complaints c
  where o.comp_org  = c.comp_org
  and c.comp_type  = 'Service') serv,
 (select count(*) from  o_complaints c
  where o.comp_org  = c.comp_org
  and c.comp_type  = 'Delivery') del,
 (select count(*) from  o_complaints c
  where o.comp_org  = c.comp_org
  and (c.comp_type  = 'Not Specified'  or c.comp_type is null) ) not_spec,
 (select count(*) from  o_complaints c) tot
from org  o
)
order by comp_org
/
0
 
LVL 1

Assisted Solution

by:sunny25
sunny25 earned 250 total points
ID: 34211626
This one will surely give you the desired result but i m not sure if this query is the best possible way considering the cost factor:
WITH main_q AS
  (SELECT comp_org           ,
    COUNT(comp_id) comp_count,
    COUNT(comp_id)*100/
    (SELECT COUNT(comp_id) FROM o_complaints
    ) comp_per
     FROM o_complaints
 GROUP BY comp_org
  ),
  service_q AS
  (SELECT comp_org           ,
    comp_type                ,
    COUNT(comp_id) serv_count,
    COUNT(comp_id)*100/
    (SELECT COUNT(comp_id) FROM o_complaints
    ) serv_per
     FROM o_complaints
    WHERE comp_id IS NOT NULL
  AND comp_type    ='Service'
 GROUP BY comp_org,
    comp_type
  ),
  prod_q AS
  (SELECT comp_org           ,
    comp_type                ,
    COUNT(comp_id) prod_count,
    COUNT(comp_id)*100/
    (SELECT COUNT(comp_id) FROM o_complaints
    ) prod_per
     FROM o_complaints
    WHERE comp_id IS NOT NULL
  AND comp_type    ='Product'
 GROUP BY comp_org,
    comp_type
  ),
  delivery_q AS
  (SELECT comp_org           ,
    comp_type                ,
    COUNT(comp_id) delv_count,
    COUNT(comp_id)*100/
    (SELECT COUNT(comp_id) FROM o_complaints
    ) delv_per
     FROM o_complaints
    WHERE comp_id IS NOT NULL
  AND comp_type    ='Delivery'
 GROUP BY comp_org,
    comp_type
  ),
  not_spec AS
  (SELECT comp_org          ,
    comp_type               ,
    COUNT(comp_id) not_count,
    COUNT(comp_id)*100/
    (SELECT COUNT(comp_id) FROM o_complaints
    ) not_per
     FROM o_complaints
    WHERE comp_id IS NOT NULL
  AND comp_type   IS NULL
 GROUP BY comp_org,
    comp_type
  )
 SELECT comp_org,
  comp_count    ,
  comp_per      ,
  NVL(
  (SELECT prod_count FROM prod_q WHERE prod_q.comp_org=main_q.comp_org
  ),0) prod,
  NVL(
  (SELECT prod_per FROM prod_q WHERE prod_q.comp_org=main_q.comp_org
  ),0) prod_per,
  NVL(
  (SELECT serv_count FROM service_q WHERE service_q.comp_org=main_q.comp_org
  ),0) serv,
  NVL(
  (SELECT serv_per FROM service_q WHERE service_q.comp_org=main_q.comp_org
  ),0) serv_per,
  NVL(
  (SELECT delv_count FROM delivery_q WHERE delivery_q.comp_org=main_q.comp_org
  ),0) delv,
  NVL(
  (SELECT delv_per FROM delivery_q WHERE delivery_q.comp_org=main_q.comp_org
  ),0) del_per,
  NVL(
  (SELECT not_count FROM not_spec WHERE not_spec.comp_org=main_q.comp_org
  ),0) not_specified,
  NVL(
  (SELECT not_per FROM not_spec WHERE not_spec.comp_org=main_q.comp_org
  ),0) not_per
   FROM main_q
ORDER BY comp_org;
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now