By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,007 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,007 IT Pros & Developers. It's quick & easy.

Calculating percentage in sql db2 with decmial precision

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a

"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

P: n/a
> 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

P: n/a
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.