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.&quot;default&quot; 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.&quot;default&quot;
)

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 &quot;totale_peso_conferimento_tipo&quot; 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?
Sounds like you want the aggregate FILTER clause:
SELECT time_bucket('1 day'::interval, c.dataora) AS dataora
     , c.codicestazione
     , c.idcomune
     , count(c.id) AS totale_conferimenti
     , sum(c.peso) AS totale_peso_conferimenti
     , sum(c.peso) FILTER (WHERE tiporifiuto = 0) AS totale_peso_conferimento_tipo
FROM   conferimenti c
GROUP  BY 1, c.codicestazione, c.idcomune;
See:

Aggregate columns with additional (distinct) filters
How to count multiple values of a column as one group?