CODEWITHSUNDEEP
sqlpostgresqlcastingaggregate-functionspercentage
user5460621
user5460621

Reputation:

Convert numeric result to percentage with decimal digits

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?

Upvotes: 0

Views: 5743

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656706

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.

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:

About round() and numeric:

Upvotes: 0

Ehsan Mohammadi
Ehsan Mohammadi

Reputation: 111

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

count(incidentnumber)::decimal*100

Upvotes: 3

Related Questions