Postgresql – What am I doing wrong with the math

postgresql

I have a table of fatalities and populations that I need to normalize to fatalities per 100K people:

CREATE TABLE all_states (
  state character varying(20) NOT NULL,
  fatalities integer NOT NULL,
  population integer NOT NULL
);

I want to calculate the fatality rate per 100,000 people, so I did:

SELECT *, (fatalities/population)*100000 AS rate 
FROM all_states;

But what I get is just 0s:

STATE          population   fatalities  rate
"Alabama"      4802740      75          0
"Alaska"       722718       39          0
"Arizona"      6482505      69          0
"Arkansas"     2937979      93          0
"California"   37691912     390         0

I feel like I'm missing something super simple here. I tried casting it as a float but that didn't change the results:

SELECT state, population, fatalities, 
       CAST(((fatalities/population)*100000) AS float) AS rate 
FROM all_states;

Best Answer

The problem here is that / operator in integer division truncates the result, check Mathematical Functions and Operators

Try with:

SELECT *,(fatalities::float/population)*100000 AS rate FROM all_states;

SELECT *,(fatalities::numeric/population)*100000 AS rate FROM all_states;

Related Solutions

Postgresql – going wrong with postgresql initdb? Why is the `UTF-8` encoding not getting enforced

If initdb is run through a run-level script of the OS. This script might not pass on the parameters.

I couldn't run initdb directly as a superuser. Gave an

initdb: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

error. After logging in as an unprivileged user I was able to initialize the db with the proper encoding.

To initilize the db, run initdb directly. The command

/usr/bin/initdb --pgdata=/var/lib/pgsql9/data/ -E 'UTF-8' \
--lc-collate='en_US.UTF-8' \
--lc-ctype='en_US.UTF-8';

was run under the non-superuser postgres.

Postgresql – Wrong return results

Your first query is equivalent of the following (notice the parentheses in the WHERE clause):

SELECT 
    host,
    plugin_id,
    min(cve) as cve,
    risk,
    synopsis,
    description,
    solution, 
    count(distinct(plugin_id)) 
from internal_network 
where 
    (risk in ('Critical','High') and description like '%password%') 
    or synopsis like '%password%' 
    or solution like '%password%' 
    or cve like '%password%' 
group by risk, plugin_id, host, synopsis, description, solution;

This is because the AND operator has higher precedence than the OR, that is, it 'glues' its operands stronger. I think what you wanted is

...
where 
    risk in ('Critical','High') 
    and 
    (
    description like '%password%' 
    or synopsis like '%password%' 
    or solution like '%password%' 
    or cve like '%password%' 
    )
...

instead.

Unless you have only ANDs (or ORs), it is always adviseable to use parentheses. It helps understand the expression, especially if it is formatted correctly.

Best Answer

Related Solutions

Postgresql – going wrong with postgresql initdb? Why is the `UTF-8` encoding not getting enforced

Postgresql – Wrong return results

Related Question