Summing column that is grouped - SQL

shazma

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

DannySlor

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Pandas summing rows grouped by another column

SQL Summing a Column Step by Step

sql query with grouped by column

SQL summing the same column with different date conditions

-SQL- Summing by other column value

SQL column to count grouped values

Percentage labels on grouped column chart with percentage summing up to 100% per group

summing column by column in java

Modify a SQL table to condense similar rows while summing up a column

SQL query not returning expected values when summing total values in a column

Problem with SQL group by and summing totals with a calculated column and sub query

Sum on a grouped count of a column in SQL Server

SQL ORDER BY - Keep column with same value grouped

SQL add IDENTITY base on grouped column value

Sum Column A While Not Summing Column B Depending On Column C - SQL Server

How to compare column values in SQL grouped by one column?

SQL - Sum of specific column grouped by a specific value of another column

SQL SELECT query with custom column, then summed and grouped by column

SQL add count column based on same table and grouped by another column

sql get duplicate column values grouped by another column

Group By column on SQL Server and concat inner join to that grouped column

SQL sum one column over time period grouped by other column

How to concatenate values of a column B grouped by the other column A SQL and R

Not summing whole column

Summing based on another column

Summing a column in Pandas Groupby

pandas is not summing a numeric column

bash summing and grouping a column

Summing a count column in PySpark