Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

calculating averages - oracle 10 sql

Posted on 2010-11-24
3
Medium Priority
?
311 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 21

Accepted Solution

by:
flow01 earned 1000 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 1000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

926 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