Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# calculating averages - oracle 10 sql

Posted on 2010-11-24
Medium Priority
309 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
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

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

LVL 20

Accepted Solution

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

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

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.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
###### Suggested Courses
Course of the Month11 days, 16 hours left to enroll

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

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