# calculating averages - oracle 10 sql

Calculating averages - oracle 10 sql

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
###### Who is Participating?

x

Commented:
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

Production Manager / Application Support ManagerCommented:
If no other experts give any help, i can help tomorrow on this. Going to bed now.
0

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.