0

Have this query returning right values where c.tiporifiuto = 0

WITH data AS (
    SELECT TIME_BUCKET_GAPFILL('1 day', c.dataora) as giorno
         , SUM(c.peso) as totale
    FROM conferimenti_001_1d AS c
    WHERE c.idcomune = 3 
      AND c.dataora >= '2020-06-01' 
      AND c.dataora <= '2020-06-12' 
      AND c.tiporifiuto = 0
    GROUP BY giorno
    ORDER BY giorno ASC
) SELECT giorno, SUM(totale) OVER (ORDER BY giorno ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
  FROM data 

returning

       giorno        |   sum   
---------------------+---------
2020-06-01 00:00:00 |   24.09
2020-06-02 00:00:00 |   48.05
2020-06-03 00:00:00 |   88.96
2020-06-04 00:00:00 |  183.78

Need same query but grouping ALSO for tiporifiuto, expected results should be

       giorno        |   sum   |  tiporifiuto
---------------------+------------------------
2020-06-01 00:00:00 |   24.09  |  0
2020-06-01 00:00:00 |   36.09  |  2
2020-06-01 00:00:00 |   13.09  |  3
2020-06-02 00:00:00 |   48.05  |  0
2020-06-02 00:00:00 |   72.18  |  2
2020-06-02 00:00:00 |   26.18  |  3
2020-06-03 00:00:00 |   xx.05  |  0
2020-06-03 00:00:00 |   xx.96  |  2
2020-06-03 00:00:00 |   xx.78  |  3

help pls

UPDATE Source data is a materialized view

CREATE MATERIALIZED VIEW public.conferimenti_001_1d
TABLESPACE pg_default
AS
   SELECT time_bucket('1 day'::interval, c.dataora) AS dataora,
   sum(c.peso) AS peso,
   c.tiporifiuto,
   c.idcomune
   FROM conferimenti c
   GROUP BY (time_bucket('1 day'::interval, c.dataora)), c.idcomune, 
   c.tiporifiuto
WITH DATA;

SOLVED WITH

WITH data AS (
    SELECT TIME_BUCKET_GAPFILL('1 day', c.dataora) as giorno, SUM(c.peso) as totale, c.tiporifiuto
    FROM conferimenti_001_1d AS c
    WHERE c.idcomune = 3 AND c.dataora >= '2020-06-01' AND c.dataora <= '2020-06-12'
    GROUP BY giorno, c.tiporifiuto
    ORDER BY giorno ASC, c.tiporifiuto ASC
) 
SELECT giorno, SUM(totale) OVER (PARTITION BY tiporifiuto ORDER BY giorno ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as totale, tiporifiuto 
FROM data
5
  • 1
    Provide source data (as CREATE TABLE + INSERT INTO) which must give shown result. PS. Maybe you need to add PARTITION BY tiporifiuto into window definition and to remove AND c.tiporifiuto = 0 from CTE simply?
    – Akina
    Jun 15 '20 at 9:54
  • Source query added, tried to add PARTITION BY but not clear how to use Jun 15 '20 at 10:06
  • update .. solved as you sayd. thanks Jun 15 '20 at 10:26
  • Don't forget to mark your own answer as correct - it might help those with a similar problem into the future? Also, if you do that, please provide your table structures and data as @Akina suggested, otherwise any answer won't be of much use. TIA! Jun 15 '20 at 16:30
  • So this is solved. Please add the solution as answer and accept it. Jun 17 '20 at 0:05
1
WITH data AS (
    SELECT TIME_BUCKET_GAPFILL('1 day', c.dataora) as giorno, SUM(c.peso) as totale, c.tiporifiuto
    FROM conferimenti_001_1d AS c
    WHERE c.idcomune = 3 AND c.dataora >= '2020-06-01' AND c.dataora <= '2020-06-12'
    GROUP BY giorno, c.tiporifiuto
    ORDER BY giorno ASC, c.tiporifiuto ASC
) 
SELECT giorno, SUM(totale) OVER (PARTITION BY tiporifiuto ORDER BY giorno ASC 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as totale, tiporifiuto 
FROM data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.