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.