Add aggregated value filtered by a condition to existing query
I have this table to collect data from IoT device:
CREATE TABLE public.conferimenti
(
id bigint NOT NULL DEFAULT nextval('conferimenti_id_seq'::regclass),
codicestazione smallint NOT NULL,
codicetag character varying(20) COLLATE pg_catalog."default" NOT NULL,
dataora timestamp without time zone NOT NULL,
peso numeric(10,2) NOT NULL DEFAULT 0.0,
tiporifiuto smallint NOT NULL,
data character varying COLLATE pg_catalog."default"
)
I am doing a MATERIALIZED VIEW which contains the SUM of weights:
SELECT time_bucket('1 day'::interval, c.dataora) AS dataora,
c.codicestazione,
c.idcomune,
sum(c.peso) AS totale_peso_conferimenti,
count(c.id) AS totale_conferimenti
FROM conferimenti c
GROUP BY 1, c.codicestazione, c.idcomune
I need to add another column called "totale_peso_conferimento_tipo" with the SUM(c.peso)
only for rows where tiporifiuto = 0
.
At the moment I'm using 2 separate queries. Maybe it can be done using window functions?
Answers 1
Sounds like you want the aggregate
FILTER
clause:See: