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,
    sum(c.peso) AS totale_peso_conferimenti,
    count( 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

Related Questions