1. Member
Join Date
Jun 2003
Posts
132

## 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
----------------------------------------------------------------

2. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135
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
•