Solved

calculating averages - oracle 10 sql

Posted on 2010-11-24
3
308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

630 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