CODEWITHSUNDEEP
sqlpostgresqlaggregate
shazma
shazma

Reputation: 55

Summing column that is grouped - SQL

I have a query:

SELECT
 date,
 COUNT(o.row_number)FILTER (WHERE o.row_number > 1 AND date_ddr IS NOT NULL AND telephone_number <> 'Anonymous' ) repeat_calls_24h
  (
  SELECT
  telephone_number,
  date_ddr,
  ROW_NUMBER() OVER(PARTITION BY ddr.telephone_number ORDER BY ddr.date) row_number,
  FROM
  table_a
  )o
GROUP BY 1

Generating the following table:

date Repeat calls_24h
17/09/2022 182
18/09/2022 381
19/09/2022 81
20/09/2022 24
21/09/2022 91
22/09/2022 110
23/09/2022 231

What can I add to my query to provide a sum of the previous three days as below?:

date Repeat calls_24h Repeat Calls 3d
17/09/2022 182
18/09/2022 381
19/09/2022 81 644
20/09/2022 24 486
21/09/2022 91 196
22/09/2022 110 225
23/09/2022 231 432

Thanks

Upvotes: 1

Views: 28

Answers (1)

DannySlor
DannySlor

Reputation: 4620

We can do it using lag.

select  "date"
       ,"Repeat calls_24h"
       ,"Repeat calls_24h" + lag("Repeat calls_24h") over(order by "date") + lag("Repeat calls_24h", 2) over(order by "date") as "Repeat Calls 3d"
from    t
date Repeat calls_24h Repeat Calls 3d
2022-09-17 182 null
2022-09-18 381 null
2022-09-19 81 644
2022-09-20 24 486
2022-09-21 91 196
2022-09-22 110 225
2022-09-23 231 432

Fiddle

Upvotes: 1

Related Questions