Convert numeric result to percentage with decimal

问题:

I have this query:

Select count(incidentnumber) as average
from incidents
Where IncidentStationGround <> firstpumparriving_deployedfromstation;

I got a result, it's something like 20,000. But how can I convert this number to a percentage? Plus, I want the results in decimal, can I?

回答1:

your query in comment should work cast count to decimal to achieve decimal percentage

count(incidentnumber)::decimal*100

回答2:

Assuming percentage of the total count:

SELECT (count(incidentnumber) FILTER (WHERE IncidentStationGround <> firstpumparriving_deployedfromstation)
        * 100.0) / count(*) AS average
FROM   incidents;

Multiply the result with a numeric constant: 100.0 (with decimal point!) to implicitly cast the bigint result of count() to numeric. You can use an explicit cast as well.

round() is optional. You can also just return lots of decimal digits, too.

• No function matches the given name and argument types

Also assuming that incidentnumber is defined NOT NULL, so count(*) is doing the same as count(incidentnumber), faster. A table definition would provide that information.

And assuming you are running the current version 9.4 (you forgot to provide that, too). Related answer with links and alternative for older versions:

• How can I simplify this game statistics query?

About round() and numeric:

• Rounding of the numeric values