Solved

calculating averages - oracle 10 sql

Posted on 2010-11-24
3
298 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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

20 Experts available now in Live!

Get 1:1 Help Now