How can I write SQL in Oracle in my case?

Question

So here are the tables -

create table person ( id number, name varchar2(50) ); create table injury_place ( id number, place varchar2(50) ); create table person_injuryPlace_map ( person_id number, injury_id number ); insert into person values (1, 'Adam'); insert into person values (2, 'Lohan'); insert into person values (3, 'Mary'); insert into person values (4, 'John'); insert into person values (5, 'Sam'); insert into injury_place values (1, 'kitchen'); insert into injury_place values (2, 'Washroom'); insert into injury_place values (3, 'Rooftop'); insert into injury_place values (4, 'Garden'); insert into person_injuryPlace_map values (1, 2); insert into person_injuryPlace_map values (2, 3); insert into person_injuryPlace_map values (1, 4); insert into person_injuryPlace_map values (3, 2); insert into person_injuryPlace_map values (4, 4); insert into person_injuryPlace_map values (5, 2); insert into person_injuryPlace_map values (1, 1); 

Here the person_injuryPlace_map table will just display both other tables.

As I wanted to show the data -

 Kitchen Pct Washroom Pct Rooftop Pct Garden Pct ----------------------------------------------------------------------- 1 14.29% 3 42.86% 1 14.29% 2 28.57% 

Here the value of the Kitchen, Washroom, Rooftop, Garden column are common incidents. Pct columns will display a percentage of the total.

How to do it in Oracle SQL?

Answer 1

You need to use the standard PIVOT request.

Depending on your version of the Oracle database version, you can do this in two ways:

Using PIVOT for version 11g and up:

 SQL> SELECT * 2 FROM 3 (SELECT c.place place, 4 row_number() OVER(PARTITION BY c.place ORDER BY NULL) cnt, 5 (row_number() OVER(PARTITION BY c.place ORDER BY NULL)/ 6 COUNT(place) OVER(ORDER BY NULL))*100 pct 7 FROM person_injuryPlace_map A 8 JOIN person b 9 ON(A.person_id = b.ID) 10 JOIN injury_place c 11 ON(A.injury_id = c.ID) 12 ORDER BY c.place 13 ) PIVOT (MAX(cnt), 14 MAX(pct) pct 15 FOR (place) IN ('kitchen' AS kitchen, 16 'Washroom' AS Washroom, 17 'Rooftop' AS Rooftop, 18 'Garden' AS Garden)); KITCHEN KITCHEN_PCT WASHROOM WASHROOM_PCT ROOFTOP ROOFTOP_PCT GARDEN GARDEN_PCT ---------- ----------- ---------- ------------ ---------- ----------- ---------- ---------- 1 14.2857143 3 42.8571429 1 14.2857143 2 28.5714286 

Using MAX and DECODE for version 10g and up:

 SQL> SELECT MAX(DECODE(t.place,'kitchen',cnt)) Kitchen , 2 MAX(DECODE(t.place,'kitchen',pct)) Pct , 3 MAX(DECODE(t.place,'Washroom',cnt)) Washroom , 4 MAX(DECODE(t.place,'Washroom',pct)) Pct , 5 MAX(DECODE(t.place,'Rooftop',cnt)) Rooftop , 6 MAX(DECODE(t.place,'Rooftop',pct)) Pct , 7 MAX(DECODE(t.place,'Garden',cnt)) Garden , 8 MAX(DECODE(t.place,'Garden',pct)) Pct 9 FROM 10 (SELECT b.ID bid, 11 b.NAME NAME, 12 c.ID cid, 13 c.place place, 14 row_number() OVER(PARTITION BY c.place ORDER BY NULL) cnt, 15 ROUND((row_number() OVER(PARTITION BY c.place ORDER BY NULL)/ 16 COUNT(place) OVER(ORDER BY NULL))*100, 2) pct 17 FROM person_injuryPlace_map A 18 JOIN person b 19 ON(A.person_id = b.ID) 20 JOIN injury_place c 21 ON(A.injury_id = c.ID) 22 ORDER BY c.place 23 ) t; KITCHEN PCT WASHROOM PCT ROOFTOP PCT GARDEN PCT ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 14.29 3 42.86 1 14.29 2 28.57 

Answer 2

If you are using Oracle 11g or higher, you can use the pivot function for your desired output.

 SELECT * FROM ( SELECT id ,place ,round(( cnt / sum(cnt) OVER ( ORDER BY NULL ) ) * 100, 2) AS pct FROM ( SELECT a.id ,a.place ,count(a.id) AS cnt FROM injury_place a JOIN person_injuryPlace_map b ON a.id = b.injury_id GROUP BY a.id ,a.place ) ) pivot(max(id) , max(pct) pct FOR place IN ( 'kitchen' AS kitchen ,'Washroom' Washroom ,'Rooftop' Rooftop ,'Garden' Garden )) 

Answer 3

You need to first take the counter and pct into the subquery, then use the max + decode function to sum both of them in the required order Check if the query helps below:

 SELECT Max(Decode(i.place,'Kitchen',cnt)) AS "Kitchecn" , Max(Decode(i.place,'Kitchen',pct)) AS "Pct" , Max(Decode(i.place,'Washroom',cnt)) AS "Washroom" , Max(Decode(i.place,'Washroom',pct)) AS "Pct" , Max(Decode(i.place,'Rooftop',cnt)) AS "Rooftop" , Max(Decode(i.place,'Rooftop',pct)) AS "Pct" , Max(Decode(i.place,'Garden',cnt)) AS "Garden" , Max(Decode(i.place,'Garden',pct)) AS "Pct" FROM (SELECT i.place , Count(pim.injury_id) AS cnt , (Count(pim.injury_id)*100/(SELECT Count(*) FROM person_injuryPlace_map)) AS pct FROM person_injuryPlace_map pim INNER JOIN injury_place i ON i.id = pim.injury_id GROUP BY i.place) 

Answer 4

I have done the following:

 select a."kitchen" , round((100/"Total")*a."kitchen") "Pct" , a."Garden" , round((100/"Total")*a."Garden") "Pct" , a."Washroom" , round((100/"Total")*a."Washroom") "Pct" , a."Rooftop" , round((100/"Total")*a."Rooftop") "Pct" from ( select sum(decode(ip.place, 'kitchen', 1, 0)) "kitchen" , sum(decode(ip.place, 'Garden', 1, 0)) "Garden" , sum(decode(ip.place, 'Washroom', 1, 0)) "Washroom" , sum(decode(ip.place, 'Rooftop', 1, 0)) "Rooftop" , sum(decode(ip.place, 'kitchen', 1, 0)) + sum(decode(ip.place, 'Garden', 1, 0)) + sum(decode(ip.place, 'Washroom', 1, 0)) + sum(decode(ip.place, 'Rooftop', 1, 0)) "Total" from person p join person_injuryPlace_map pim on pim.person_id = p.id join injury_place ip on ip.id = pim.injury_id ) a