467,110 Members | 1,411 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,110 developers. It's quick & easy.

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" <ma********@gmail.com> 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" <ma********@gmail.com> 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.

Similar topics

7 posts views Thread by Conax | last post: by
lee123
4 posts views Thread by lee123 | last post: by
1 post views Thread by zufie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.