Division in postgreSQL shows incorrect result

0

I have the query that returns the value of an index that is the result of an operation between two columns, the result I want in decimals and shows it that way, what happens is that the result is not correct: here I leave my query:

SELECT dep.nombre as departamento,
mun.nombre,can.nombre,cas.nombre,
sum(cap.total_mosquitos) as total_mosquitos,
sum(cap.total_anopheles) AS total_anopheles,
sum(cap.casa_positiva) AS casa_positiva,
sum(cap.casa_inspeccionada) AS casa_inspeccionada,
SUM(CASE casa_inspeccionada WHEN 0 THEN null ELSE 
(total_mosquitos::float/casa_inspeccionada::float) END) AS indice_casa
FROM pl_captura_anopheles cap
INNER JOIN ctl_caserio cas on (cap.id_caserio =cas.id)
INNER JOIN ctl_canton can on (cas.id_canton = can.id)
INNER JOIN ctl_municipio mun on (can.id_municipio = mun.id)
INNER JOIN ctl_departamento dep on (dep.id=mun.id_departamento)
WHERE (EXTRACT(YEAR FROM cap.fecha)=2018) 
GROUP By dep.nombre,mun.nombre,can.nombre,cas.nombre

and the result it shows me:

In the first row you can see the incorrect result 110/70 = 3.16?

    
asked by Igmer Rodriguez 09.05.2018 в 17:01
source

1 answer

0

The problem was that I was adding the division results of each row, so what I have done is to add the columns first and then divide that number:

(sum(total_mosquitos)::numeric/ sum(casa_inspeccionada)::numeric)as indice
    
answered by 09.05.2018 / 17:31
source