CODEWITHSUNDEEP
sqlsql-servergroup-bycountsum
Adam Levitt
Adam Levitt

Reputation: 10476

SQL Grouped Results with Counts and Cumulative Counts

I have the following data in my 'user' table:

user_id | create_timestamp
1         2017-08-01
2         2017-08-01
3         2017-08-02
4         2017-08-03
5         2017-08-03
6         2017-08-03
7         2017-08-04
8         2017-08-04
9         2017-08-04
10        2017-08-04

I want to create a SQL query that has three columns: 1. Grouped results by create_timestamp 2. A count of the results by date 3. A cumulative count as the date goes on.

Here's what the result set should look like:

create_timestamp daily cumulative
2017-08-01         2      2
2017-08-02         1      3
2017-08-03         3      6
2017-08-04         4      10

Upvotes: 1

Views: 2225

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use this query.

DECLARE @UserLog TABLE (user_id INT , create_timestamp DATE)
INSERT INTO @UserLog
VALUES
(1,'2017-08-01'),
(2,'2017-08-01'),
(3,'2017-08-02'),
(4,'2017-08-03'),
(5,'2017-08-03'),
(6,'2017-08-03'),
(7,'2017-08-04'),
(8,'2017-08-04'),
(9,'2017-08-04'),
(10,'2017-08-04')

;WITH T AS (
    SELECT create_timestamp, COUNT(*) daily  FROM @UserLog
    GROUP BY create_timestamp)
    SELECT 
        create_timestamp, 
        daily, 
        SUM(daily) OVER( ORDER BY create_timestamp ASC  
                            ROWS UNBOUNDED PRECEDING ) cumulative  
FROM T

Result

create_timestamp daily       cumulative
---------------- ----------- -----------
2017-08-01       2           2
2017-08-02       1           3
2017-08-03       3           6
2017-08-04       4           10

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You would use window functions for this:

select create_timestamp, count(*) as cnt,
       sum(count(*)) over (order by create_timestamp) as cumulative
from t
group by create_timestamp
order by create_timestamp;

This functionality is available in SQL Server 2012+.

Note: You may need to extract the date from the time stamp:

select convert(date, create_timestamp) as dte, count(*) as cnt,
       sum(count(*)) over (order by convert(date, create_timestamp)) as cumulative
from t
group by convert(date, create_timestamp)
order by convert(date, create_timestamp);

Upvotes: 4

Related Questions