SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct
-- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct -- simpler
FROM patients;
Do not use a subselect. Both aggregates can be derived from the same query. Cheaper.
Also, this is not a case for window functions, since you want to compute a single result, and not one result per row.
Cast to any numeric type that supports fractional digits, like @a_horse already explained.
Since you want to round()
to two fractional digits I suggest numeric
(which is the same as decimal
in Postgres).
It's enough to cast one value involved in a calculation, preferably the first. Postgres automatically settles for the type that does not lose information.
Or, simpler yet: since we multiply anyway, use a numeric constant that's coerced to numeric
automatically because of the decimal point (100.0
).
It's generally a good idea to multiply before you divide. This typically minimizes rounding errors and is cheaper.
In this case, the first multiplication (count(refinst) * 100
) can be computed with cheap and exact integer
arithmetic. Only then we cast to numeric
and divide by the next integer
(which we do not cast additionally).
NULLIF(count(*), 0)
prevents division by zero (raising an exception). We get NULL as (unknown) percentage if there are no rows at all.
Rounded to two fractional digits:
SELECT round((count(refinst) * 100)::numeric / NULLIF(count(*), 0), 2) AS refinst_pct
FROM patients;
Okay, well, since nobody else chimed in and I needed to get it done, I took one last shot at it. Not quite sure on the why of it all, but I used basically the entire query as a subquery in the FROM clause, and then ran the report against that, and that seemed to do it...
SELECT
[VendorCusts].[VendorPaid],
[VendorCusts].[DE?],
[VendorCusts].[IC?],
[VendorCusts].[AG?],
[VendorCusts].[GB?],
COUNT(VendorCustID) AS [CustCount]
FROM
(
SELECT DISTINCT
[Plan Revenue Expense].[Check To] AS [VendorPaid],
[Support Provider].[DE?],
[Support Provider].[IC?],
[Support Provider].[Agency?] AS [AG?],
[Support Provider].[GeneralBus?] AS [GB?],
Customer.CustID AS [VendorCustID],
Customer.LName AS [VendorCustLName],
Customer.FName AS [VendorCustFName]
FROM
(((Customer
INNER JOIN Plan ON Customer.CustID=Plan.CustID)
INNER JOIN [Plan Revenue] ON [Plan].[Plan ID]=[Plan Revenue].[PlanID])
INNER JOIN [Plan Revenue Expense] ON [Plan Revenue].[Rev ID]=[Plan Revenue Expense].[RevID])
LEFT JOIN [Support Provider] ON [Plan Revenue Expense].[SP]=[Support Provider].[ID]
WHERE
(
(
([Plan Revenue Expense].[First Day]>=[Expense Start "MM/DD/YYYY"])
AND
([Plan Revenue Expense].[First Day]<=[Expense End "MM/DD/YYYY"])
)
OR
(
([Plan Revenue Expense].[Last Day]>=[Expense Start "MM/DD/YYYY"])
AND
([Plan Revenue Expense].[Last Day]<=[Expense End "MM/DD/YYYY"])
)
)
AND NOT
(
[Plan Revenue].[Service]='111' OR
[Plan Revenue].[Service]='222' OR
[Plan Revenue].[Service] LIKE '333*'
)
AND NOT Customer.[Inactive?]=TRUE
ORDER BY
[Plan Revenue Expense].[Check To],
Customer.LName,
Customer.FName
) AS [VendorCusts]
GROUP BY
[VendorCusts].[VendorPaid],
[VendorCusts].[DE?],
[VendorCusts].[IC?],
[VendorCusts].[AG?],
[VendorCusts].[GB?]
HAVING
NOT ([VendorCusts].[DE?]=TRUE OR [VendorCusts].[IC?]=TRUE)
ORDER BY
[VendorCusts].[DE?] ASC,
[VendorCusts].[IC?] ASC,
[VendorCusts].[AG?] ASC,
[VendorCusts].[GB?] ASC,
[VendorCusts].[VendorPaid] ASC;
This is more or less what I finally came up with and it appears to get the job done. Checked a few data points and they seemed to agree with the slightly more verbose version w/o a Count() function. So, I think the results are good.
Then I abstracted out the start and end of the date range so I could run a few different versions. And added a few other parameters I needed...
Seems like it's working as intended, if a bit kludge-y. Sometimes it's better to be 'right' than 'pretty.' ;)
Best Answer
You are just missing the GROUP BY
YOUR QUERY WITH GROUP BY
SAMPLE DATA
SAMPLE DATA LOADED
YOUR QUERY WITH GROUP BY EXECUTED
GIVE IT A TRY !!!