Mainframe, MVS and zOS Discussion

Posted: **Tue Dec 30, 2014 1:54 pm**

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?

Posted: **Tue Dec 30, 2014 4:49 pm**

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.

Mainframe, MVS and zOS Discussion

Find the percentag in DB2.

Find the percentag in DB2.

Re: Find the percentag in DB2.