# Thread: How to calculate percentage in SQL DB2 query

1. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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```

3. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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?

5. Registered User
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. Registered User
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
•