Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    8

    Unanswered: How to calculate percentage in SQL DB2 query

    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 ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2011
    Posts
    8

    Thanks

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

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by prashant.gadekar View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2011
    Posts
    8

    Thanks

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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •