Home » SQL & PL/SQL » SQL & PL/SQL » Calculation of percentage based on total
Calculation of percentage based on total Sat, 30 January 2010 13:01
 sean1970 Messages: 13Registered: January 2010 Junior Member
I have written a query to get output in %. I need to know is there any other way of getting the same output with more efficient query.

```
CREATE TABLE time_summury
(
COL_TYPE  	VARCHAR2(50 CHAR),
JAN_TOTAL     NUMBER,
FEB_TOTAL     NUMBER,
MAR_TOTAL     NUMBER,
APR_TOTAL     NUMBER,
MAY_TOTAL     NUMBER,
JUN_TOTAL     NUMBER,
JUL_TOTAL     NUMBER,
AUG_TOTAL     NUMBER,
SEP_TOTAL     NUMBER,
OCT_TOTAL     NUMBER,
NOV_TOTAL     NUMBER,
DEC_TOTAL     NUMBER,
TOTAL         NUMBER
);

INSERT INTO time_summury(col_type, jan_total, feb_total, mar_total, apr_total,
may_total, jun_total, jul_total, aug_total, sep_total, oct_total, nov_total, dec_total,
total ) VALUES (
'Type 1', 0, 80, 138, 88, 108, 0, 0, 0, 0, 0, 0, 0, 414);

INSERT INTO time_summury (col_type, jan_total, feb_total, mar_total, apr_total,
may_total, jun_total, jul_total, aug_total, sep_total, oct_total, nov_total, dec_total,
total ) VALUES (
'Type 2', 0, 0, 0, 44, 0, 0, 0, 0, 0, 0, 0, 0, 44);

INSERT INTO time_summury(col_type, jan_total, feb_total, mar_total, apr_total,
may_total, jun_total, jul_total, aug_total, sep_total, oct_total, nov_total, dec_total,
total ) VALUES (
'Type 3', 0, 80, 46, 0, 36, 0, 0, 0, 0, 0, 0, 0, 162);

commit;

SELECT * FROM time_summury;

Type	JAN FEB  MAR  APR   MAY   JUN    TOTAL
-----------------------------------------------
Type 1  0   80   138   88   108    0       414
Type 2  0   0     44    0     0    0        44
Type 3  0   80    46    0    36    0       162

WITH t1 AS
(SELECT SUM(jan_total) AS jan_sum,SUM(feb_total) AS feb_sum,
SUM(mar_total) AS mar_sum,SUM(apr_total) AS apr_sum,
SUM(may_total) AS may_sum,SUM(jun_total) AS jun_sum,
SUM(jul_total) AS jul_sum,SUM(aug_total) AS aug_sum,
SUM(sep_total) AS sep_sum,SUM(oct_total) AS oct_sum,
SUM(nov_total) AS nov_sum,SUM(dec_total) AS dec_sum,
SUM(total) AS row_sum
FROM time_summury)
SELECT col_type,
NVL(ROUND((jan_total*100)/NULLIF(t1.jan_sum,0),1),0) AS jan,
NVL(ROUND((feb_total*100)/NULLIF(t1.feb_sum,0),1),0) AS feb,
NVL(ROUND((mar_total*100)/NULLIF(t1.mar_sum,0),1),0) AS mar,
NVL(ROUND((apr_total*100)/NULLIF(t1.apr_sum,0),1),0) AS apr,
NVL(ROUND((may_total*100)/NULLIF(t1.may_sum,0),1),0) AS may,
NVL(ROUND((jun_total*100)/NULLIF(t1.jun_sum,0),1),0) AS jun,
NVL(ROUND((total*100)/NULLIF(t1.row_sum,0),1),0) AS total
FROM time_summury,t1

--output from above query.....

Type	JAN FEB  MAR  APR     MAY   JUN    TOTAL
-------------------------------------------------
Type 1  0   50    75   66.7    75    0       66.8
Type 2  0   0      0   33.3     0    0        7.1
Type 3  0   50    25      0    25    0       26.1

DROP TABLE time_summury;

```

Can someone help here..

Regards,
Sean
Re: Calculation of percentage based on total [message #441321 is a reply to message #441320] Sat, 30 January 2010 13:38
 Michel Cadot Messages: 64956Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
```SQL> select col_type,
2         nvl(100*ratio_to_report(jan_total) over(),0) jan,
3         nvl(100*ratio_to_report(feb_total) over(),0) feb,
4         nvl(100*ratio_to_report(mar_total) over(),0) mar,
5         nvl(100*ratio_to_report(apr_total) over(),0) apr,
6         nvl(100*ratio_to_report(may_total) over(),0) may,
7         nvl(100*ratio_to_report(jun_total) over(),0) jun,
8         nvl(100*ratio_to_report(jul_total) over(),0) jul,
9         nvl(100*ratio_to_report(aug_total) over(),0) aug,
10         nvl(100*ratio_to_report(sep_total) over(),0) sep,
11         nvl(100*ratio_to_report(nov_total) over(),0) nov,
12         nvl(100*ratio_to_report(dec_total) over(),0) dec,
13         nvl(100*ratio_to_report(total) over(),0) total
14  from time_summury;
TYPE    JAN  FEB  MAR  APR  MAY  JUN  JUL  AUG  SEP  NOV  DEC TOTAL
------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
Type 1    0   50   75 66.7   75    0    0    0    0    0    0  66.8
Type 2    0    0    0 33.3    0    0    0    0    0    0    0   7.1
Type 3    0   50   25    0   25    0    0    0    0    0    0  26.1

3 rows selected.```

Regards
Michel
Re: Calculation of percentage based on total [message #441323 is a reply to message #441321] Sat, 30 January 2010 13:49
 sean1970 Messages: 13Registered: January 2010 Junior Member
Thanks again Michel. I never used these analytical functions. Need to study a lot
 Previous Topic: ORA-19025 on Binary XML Table Unique Constraint (merged by CM) Next Topic: a complex query returning data in a refcursor....
Goto Forum:

Current Time: Mon Jun 26 22:36:37 CDT 2017

Total time taken to generate the page: 0.09134 seconds