-
percent calculation
Hi,
I am having a problem to display % value of the total count in the query.
The query returns a PIN_COUNT value for specific App_Version, but I would also like to display a % of the total PIN_COUNT. How can I accomplish this?
Thanks
Desired results:
RELAY | APP_VERSION | PIN_COUNT | % |
1 3.6 40 45
1 3.7 20 22.5
Current query:
----------------------------------------------------------------
SELECT /* + index(D device_info_carrier) */
d.relay, nvl(d.app_version,'Unknown') App_Version, count (x.pin) PIN_COUNT
FROM bblg.device_info@prldw d,
(SELECT /* + index(C cdr_sum_dte) */
DISTINCT c.pin, c.rownum
FROM bblg.cdr_summary@prldw c
WHERE c.dte between to_date('03-Dec-2003') and to_date('04-Dec-2003')
) x
WHERE d.pin = x.pin
AND d.carrier = 'NexTel'
GROUP BY d.carrier, d.relay, App_Version
----------------------------------------------------------------
-
You have to use Ratio_To_Report Analytic function to get the Percentage. Below is an example. You can change your SQL according to your need.
11:35:07 SBL1>DESC SALES
Name Null? Type
------------ -------- ---------------------
PRODUCT VARCHAR2(20)
SALES NUMBER
11:35:11 SBLPTH1>SELECT * FROM SALES ;
PRODUCT SALES
-------------------- ----------
P1 280
P2 400
P3 200
P4 300
P5 190
P6 310
6 rows selected.
Elapsed: 00:00:00.50
11:35:58 SBL1>SELECT PRODUCT, SALES ,
TO_CHAR((RATIO_TO_REPORT(SALES) OVER())*100,999.99) AS SALES_PER
FROM SALES ;
PRODUCT SALES SALES_P
-------------------- ---------- -------
P1 280 16.67
P2 400 23.81
P3 200 11.90
P4 300 17.86
P5 190 11.31
P6 310 18.45
6 rows selected.
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|