How to calculate percentage in SQL DB2 query

prashant.gadekar · #1 (**permalink**) 03-31-11, 08:30

Hi,

I am having following table structure and sample values:

Code:

 ----------------------------------------------------------
 APP_ID       DATE        HOUR    STATUS_CODE    HITS    
 ----------------------------------------------------------
 APP1       2011-15-03    12    200        50
 APP1       2011-15-03    13    200        10
 APP1       2011-15-03    14    200        5
 APP1       2011-15-03    12    300         5    
 APP1       2011-15-03    13    300         1
 APP1       2011-15-03    14    300         1
 APP1       2011-15-03    12    400         1
 APP1       2011-15-03    13    400         1
 APP2       2011-15-03    12    200        10
 APP2       2011-15-03    13    200        20
 APP2       2011-15-03    14    200        40
 APP2       2011-15-03    12    300         15    
 APP2       2011-15-03    13    300         5
 APP2       2011-15-03    14    300         5
 APP2       2011-15-03    12    400         6
 APP2       2011-15-03    13    400         1
 APP3       2011-15-03    13    200        30
 APP3       2011-15-03    14    200        10
 ----------------------------------------------------------

Above table contains log for of number hits for web-application at given date at given hour.

I have calculate the following result from above table:

Code:

----------------------------------------------------------
APP_ID        HITS         PERCENTAGE
----------------------------------------------------------
APP1        65        36.11
APP2        75        41.67
APP3        40         22.22
----------------------------------------------------------

Above table is result for number of hits for STATUS_CODE = 200 group by APP_ID.

we can get the number of hits by simple query:

Code:

select APP_ID, sum(HITS) As HITS FROM SUMMARY WHERE STATUS_CODE = 200 GROUP BY APP_ID;

How to calculate the PERCENTAGE in above result ?

r937 · #2 (**permalink**) 03-31-11, 08:51

Code:

SELECT app_id
     , SUM(hits) AS hits 
     , 100.0 * SUM(hits) /
       ( SELECT SUM(hits)
           FROM summary 
          WHERE status_code = 200 ) AS percentage
  FROM summary 
 WHERE status_code = 200 
GROUP 
    BY app_id

prashant.gadekar · #3 (**permalink**) 03-31-11, 10:02

Thanks you very much for quick reply.
Hurray it work !!

I was wondering will it be fast for millions of record ?

r937 · #4 (**permalink**) 03-31-11, 10:07

Quote:

Originally Posted by prashant.gadekar

I was wondering will it be fast for millions of record ?

sure

what happened when you tested it on a table with millions of rows?

prashant.gadekar · #5 (**permalink**) 03-31-11, 10:26

Currently I am not having that much of data on my test machine.

I have as much as 1000 Records. It is giving response in real time.

So here no worries. Once I deploy it, then only I will able to get the real performance.
Once I get the response time I will defiantly share ..

Thanks again !!

tonkuma · #6 (**permalink**) 03-31-11, 11:13

Here is another example.
(I prefer to use capital letters for kewords and lower-case letters for object names(table, column, etc.).)

Code:

------------------------------ Commands Entered ------------------------------
WITH summary(app_id , date , hour , status_code , hits) AS (
VALUES
  ('APP1' , '2011-15-03' , 12 , 200 , 50)
, ('APP1' , '2011-15-03' , 13 , 200 , 10)
, ('APP1' , '2011-15-03' , 14 , 200 ,  5)
, ('APP1' , '2011-15-03' , 12 , 300 ,  5)
, ('APP1' , '2011-15-03' , 13 , 300 ,  1)
, ('APP1' , '2011-15-03' , 14 , 300 ,  1)
, ('APP1' , '2011-15-03' , 12 , 400 ,  1)
, ('APP1' , '2011-15-03' , 13 , 400 ,  1)
, ('APP2' , '2011-15-03' , 12 , 200 , 10)
, ('APP2' , '2011-15-03' , 13 , 200 , 20)
, ('APP2' , '2011-15-03' , 14 , 200 , 40)
, ('APP2' , '2011-15-03' , 12 , 300 , 15)
, ('APP2' , '2011-15-03' , 13 , 300 ,  5)
, ('APP2' , '2011-15-03' , 14 , 300 ,  5)
, ('APP2' , '2011-15-03' , 12 , 400 ,  6)
, ('APP2' , '2011-15-03' , 13 , 400 ,  1)
, ('APP3' , '2011-15-03' , 13 , 200 , 30)
, ('APP3' , '2011-15-03' , 14 , 200 , 10)
)
SELECT app_id
     , SUM(hits)                 AS hits
     , 000000000000000100.00
       * SUM(hits)
       / SUM( SUM(hits) ) OVER() AS percentage
 FROM  summary
 WHERE status_code = 200
 GROUP BY
       app_id
;
------------------------------------------------------------------------------

APP_ID HITS        PERCENTAGE                       
------ ----------- ---------------------------------
APP1            65                             37.14
APP2            70                             40.00
APP3            40                             22.85

  3 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech