Hi everybody,
I’ve made a query to get back a percentage (result is <= 1).
But PostgreSQL returns a BigInt instead of a Decimal type. As a result the value returned is rounded to 0 or 1
How can I force to get a Decimal result?
The query:
SELECT (SELECT COUNT(t9.fld_id)
FROM tbl_proposals AS t9
WHERE t9.fld_date_entered BETWEEN '1999-01-01' AND '2009-12-01'
AND t9.fld_status = 'Won')
/COUNT(t10.fld_id)
FROM tbl_proposals AS t10
WHERE t10.fld_date_entered BETWEEN '1999-01-01' AND '2009-12-01'
Hi Foobrother,
Foobrother:
How can I force to get a Decimal result?
The query:
SELECT (SELECT COUNT(t9.fld_id)
FROM tbl_proposals AS t9
WHERE t9.fld_date_entered BETWEEN '1999-01-01' AND '2009-12-01'
AND t9.fld_status = 'Won')
/COUNT(t10.fld_id)
FROM tbl_proposals AS t10
WHERE t10.fld_date_entered BETWEEN ‘1999-01-01’ AND ‘2009-12-01’
You can cast the result to a Numeric or Float by adding ::float or ::numeric or more specifically ::numeric(5,2).
So your query will look like this:
SELECT ((SELECT COUNT(t9.fld_id)
FROM tbl_proposals AS t9
WHERE t9.fld_date_entered BETWEEN '1999-01-01' AND '2009-12-01'
AND t9.fld_status = 'Won')
/COUNT(t10.fld_id))::numeric(5,2)
FROM tbl_proposals AS t10
Hope this helps.
ROCLASI:
Hi Foobrother,
You can cast the result to a Numeric or Float by adding ::float or ::numeric or more specifically ::numeric(5,2).
So your query will look like this:
SELECT ((SELECT COUNT(t9.fld_id)
FROM tbl_proposals AS t9
WHERE t9.fld_date_entered BETWEEN '1999-01-01' AND '2009-12-01'
AND t9.fld_status = 'Won')
/COUNT(t10.fld_id))::numeric(5,2)
FROM tbl_proposals AS t10
Hope this helps.
It works!
Thank you ROCLASI have a good weekend ![Mr. Green :mrgreen:]()