467,110 Members | 1,411 Online

# Calculating percentage in sql db2 with decmial precision

 Hi I am writing a sql query (added below) to calulate percentage. Its returning an integer value whereas I would like to get a float value (with 2 decimal precision). The query is: (SELECT (COUNT(*) * 100) / (SELECT COUNT(*) FROM HEALTHCAREDB.PATIENT_DIABETIC_TYPE) FROM HEALTHCAREDB.PATIENT_DIABETIC_TYPE WHERE HEALTHCAREDB.PATIENT_DIABETIC_TYPE.DIABETES_TYPE=' GDM' How do I get a float value returned? Thanks Mahesh May 8 '06 #1
• viewed: 26303
Share:
4 Replies
 It's assuming INTEGER because all numbers involved are INTEGER. To make it return a FLOAT, you must supply a FLOAT: (SELECT (COUNT(*) * 100.0) / B. May 8 '06 #2
 "Mahesh S" wrote in message news:11**********************@v46g2000cwv.googlegr oups.com... Hi I am writing a sql query (added below) to calulate percentage. Its returning an integer value whereas I would like to get a float value (with 2 decimal precision). The query is: (SELECT (COUNT(*) * 100) / (SELECT COUNT(*) FROM HEALTHCAREDB.PATIENT_DIABETIC_TYPE) FROM HEALTHCAREDB.PATIENT_DIABETIC_TYPE WHERE HEALTHCAREDB.PATIENT_DIABETIC_TYPE.DIABETES_TYPE=' GDM' How do I get a float value returned? Your problem is that you are dividing an integer by an integer; this is inevitably going to return another integer. If you change the constant 100 in the first SELECT to 100.0, i.e. (SELECT (COUNT(*) * 100.0) / (SELECT COUNT(*) FROM HEALTHCAREDB.PATIENT_DIABETIC_TYPE) FROM HEALTHCAREDB.PATIENT_DIABETIC_TYPE WHERE HEALTHCAREDB.PATIENT_DIABETIC_TYPE.DIABETES_TYPE=' GDM' your result will be a decimal number. DB2 then treats the result of the first SELECT as a decimal and decides to represent a decimal divided by an integer as a decimal. Another approach would be to use a decimal function in the denominator to make it a decimal, i.e. (SELECT (COUNT(*) * 100) / DECIMAL((SELECT COUNT(*) FROM HEALTHCAREDB.PATIENT_DIABETIC_TYPE),9,2) FROM HEALTHCAREDB.PATIENT_DIABETIC_TYPE WHERE HEALTHCAREDB.PATIENT_DIABETIC_TYPE.DIABETES_TYPE=' GDM' That would also return a decimal result. -- Rhino May 8 '06 #3
 > float value (with 2 decimal precision). You need to make up your mind. Floats do not have decimal precision, nor can you specify the number of "binary digits" after the binary point. For that you will need to use Decimal. Use the FLOAT function to get a float, the DECIMAL function to get a decimal. "Mahesh S" wrote in message news:11**********************@v46g2000cwv.googlegr oups.com... Hi I am writing a sql query (added below) to calulate percentage. Its returning an integer value whereas I would like to get a float value (with 2 decimal precision). The query is: (SELECT (COUNT(*) * 100) / (SELECT COUNT(*) FROM HEALTHCAREDB.PATIENT_DIABETIC_TYPE) FROM HEALTHCAREDB.PATIENT_DIABETIC_TYPE WHERE HEALTHCAREDB.PATIENT_DIABETIC_TYPE.DIABETES_TYPE=' GDM' How do I get a float value returned? Thanks Mahesh May 10 '06 #4
 Thanks guys.. the decimal function was able to do the job for me for the time being. Really appreciate teh help. Cheers May 10 '06 #5

### This discussion thread is closed

Replies have been disabled for this discussion.