It is possible to count how many records there are in a query, I want them to appear as one more column since the query must return a single row.
SELECT dep.nombre as departamento,
sum(cap.total_mosquitos) as total_mosquito,
sum(cap.total_anopheles) AS total_anopheles,
sum(cap.casa_positiva) AS casa_positiva,
sum(cap.casa_inspeccionada) AS casa_inspeccionada,
sum(cap.componente_inspeccionado) AS componenente_inspeccionado,
sum(cap.tiempo_colecta) AS tiempo_colecta,
count(cap.id_tipo_actividad) AS programa_regular
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)
inner join pl_tipo_captura tc on(cap.id_tipo_captura=tc.id)
inner join ctl_pl_tipo_actividad act on(cap.id_tipo_actividad=act.id)
where (EXTRACT(YEAR FROM cap.fecha)=2018)
group by dep.nombre
In the count I want to know how many there are of type ctl_type activity 1 and how many of type 2
You add the columns with an example case:
The idea is that the case function returns the value 1 when it is the expected result so that it can be added with the SUM function, if it is not the expected value it is left at 0 so that it does not affect the sum.
NOTE: check the syntax of the case clause since I did it on the fly.
Greetings.
You can use the Compute By clause , but it gives you the result in another dataset instead of adding it as a row which is what you want. To achieve this, use a Union all removing the column you group by.
That is the general idea, to reach your requirement you must adapt the part of the second select. It was not very clear to me what exactly you want to tell