Home » SQL & PL/SQL » SQL & PL/SQL » Calculation of percentage based on total
Calculation of percentage based on total [message #441320] Sat, 30 January 2010 13:01 Go to next message
sean1970
Messages: 13
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous message
sean1970
Messages: 13
Registered: January 2010
Junior Member
Thanks again Michel. I never used these analytical functions. Need to study a lot Smile
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: Thu Sep 29 05:36:21 CDT 2016

Total time taken to generate the page: 0.18848 seconds