Find the percentag in DB2.

[Deepika]

Hello All,

I need to find out the percentage of a group of accessories in a given table. For example, let's assume ACCER is the main table and has got 100000 records. In this 30000 are with ACCCODE = 'STAPLE', if I know the numbers I will find out the percentage (30000/100000) x 100. But I don’t know the total number of records for both i.e. how many records are in table and how many are with STAPLE…
How can I do it in DB2 itself?

[Anuj Dhawan]

Hi,

If I got it correctly and with the information in hand, try this:

Code: Select all

SELECT (SUM(CASE WHEN ACCCODE = 'STAPLE'
                THEN DECIMAL((1),6,2)  
                ELSE DECIMAL((0),6,2)  
            END) / COUNT(*)) * 100      
 FROM TABLE;

This will give you a precision to two digits after decimal.