percent calculation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: percent calculation

  1. #1
    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. #2
    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
  •  



Click Here to Expand Forum to Full Width