Convert numeric result to percentage with decimal digits

Question

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?

Answer 1

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

count(incidentnumber)::decimal*100

Answer 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. (Or cast explicitly.)

round() is optional. You get many decimal places without it.

• No function matches the given name and argument types

Assuming that incidentnumber is defined NOT NULL, so count(*) does the same as count(incidentnumber), just a bit faster. (A table definition would provide that information.)

Assuming Postgres 9.4 or later.
Related answer with links and alternative for older versions:

• Aggregate columns with additional (distinct) filters

About round() and numeric:

• Rounding of the numeric values