CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloperpercentageplsqldeveloper
user3098728
user3098728

Reputation: 131

Convert integer into percentage

I don't know how to convert integer into percentage, please help me. Thank you

Here's the query:

SELECT 'Data' || ',' ||
       TO_CHAR(D.DTIME_DAY,'MM/dd/yyyy') || ',' ||
       NVL(o.CNT_OPENED,0) || ',' || --as cnt_opened
       NVL(c.CNT_CLOSED,0)           --as cnt_closed
  FROM OWNER_DWH.DC_DATE d
  LEFT JOIN (SELECT TRUNC(t.CREATE_TIME, 'MM') AS report_date,
                    count(*) AS cnt_opened
               FROM APP_ACCOUNT.OTRS_TICKET t
               WHERE t.CREATE_TIME BETWEEN SYSDATE -120 AND SYSDATE
               GROUP BY TRUNC(t.CREATE_TIME, 'MM')) o
    ON d.DTIME_DAY=o.REPORT_DATE
  LEFT JOIN (SELECT TRUNC(t.CLOSE_TIME, 'MM') as report_date,
                    count(*) AS cnt_closed
               FROM APP_ACCOUNT.OTRS_TICKET t
               WHERE t.CLOSE_TIME BETWEEN SYSDATE -120 AND SYSDATE
               GROUP BY TRUNC(t.CLOSE_TIME, 'MM')) c
    ON D.DTIME_DAY=c.REPORT_DATE
  WHERE d.DTIME_DAY BETWEEN SYSDATE -120 AND TRUNC(SYSDATE) -1 AND
        d.DTIME_DAY = TRUNC(d.DTIME_DAY, 'MM') AND
        TRUNC(d.DTIME_DAY,'MM')= d.DTIME_DAY
  ORDER BY D.DTIME_DAY;

The output of that query:

 Data,10/01/2013,219,201
 Data,11/01/2013,249,234
 Data,12/01/2013,228,224
 Data,01/01/2014,269,256

example output that I need is like this:

 Data,10/01/2013,219, 52%, 201, 45%
 Data,11/01/2013,249, 75%, 234, 60%
 .......
 ........

Formula:

create_time + close time = total / create_time (for cnt_opened each column) = percentage
create_time + close time = total / close_time (for cnt_closed each column) = percentage

Upvotes: 0

Views: 592

Answers (1)

Joel
Joel

Reputation: 2257

Try this:

Basically just add the total of CNT_OPENED and CNT_CLOSED, then whichever you want to take the percentage of, multiply that by 100 and divide by the sum.

For instance, CNT_OPENED = 219 and CNT_CLOSED = 201 so the total is 420. Multiply CNT_OPENED by 100 and then divide by 420 -> (219 * 100) / 420 = 52. Do the same thing with CNT_CLOSED.

Note that this WILL result in an exception if both CNT_OPENED and CNT_CLOSED are 0.

SELECT 'Data'
||','||TO_CHAR(D.DTIME_DAY,'MM/dd/yyyy')
||','||NVL(o.CNT_OPENED,0) --as cnt_opened
||','||(NVL(o.CNT_OPENED,0) * 100) / (NVL(o.CNT_OPENED,0) + NVL(o.CNT_CLOSED,0)) || '%'
||','||NVL(c.CNT_CLOSED,0) --as cnt_closed
||','||(NVL(o.CNT_CLOSED,0) * 100) / (NVL(o.CNT_OPENED,0) + NVL(o.CNT_CLOSED,0)) || '%'

That will also potentially give you a million decimal places, so if you only want to take it out to a couple, simply use the TRUNC function and specify your precision (2 decimal places in this case):

TRUNC((NVL(o.CNT_OPENED,0) * 100) / (NVL(o.CNT_OPENED,0) + NVL(o.CNT_CLOSED,0)), 2)

Upvotes: 2

Related Questions