Search code examples
sqlsql-serverdatecountwindow-functions

How to Add one row to another

I have a set of data which is shown in picture:

Set of Data

I've tried adding January to February using the lead function which was correct shown in the picture below again:

Result After using LEAD

And I thought it will add and add to the next row like a for loop but it did not.

I am asking for help on how to add the data again and again like a for loop would do. I've pasted the code I used below.

;WITH MonthActiveUsers AS (

        SELECT MonthLogin, COUNT(MonthLogin) AS ActiveUsers
        FROM #TEMPDistinctActiveUsersPerMonth DAUPM
        GROUP BY MonthLogin     
)  
SELECT MAU.MonthLogin, (LEAD(MAU.ActiveUsers,0) OVER (ORDER BY MAU.MonthLogin ASC) + LEAD(MAU.ActiveUsers,1) OVER (ORDER BY MAU.MonthLogin ASC)) AS [Count of Active Users]
FROM 
    (   SELECT CASE 
                    WHEN MonthLogin = 'January'
                    THEN '2020-01-01'
                    WHEN MonthLogin = 'February'
                    THEN '2020-02-01'
                    WHEN MonthLogin = 'March'
                    THEN '2020-03-01'
                    WHEN MonthLogin = 'April'
                    THEN '2020-04-01'
                    WHEN MonthLogin = 'May'
                    THEN '2020-05-01'
                    WHEN MonthLogin = 'June'
                    THEN '2020-06-01'
                    ELSE NULL
                END AS [MonthLogin]
                ,ActiveUsers
        FROM MonthActiveUsers MAU
        ) MAU

The desired result would be:

January = 3313
February = 3349
March = 3398
April = 3421
May = 3437
June = 3444
Solution

  • I think that you want a window sum:

    WITH ...
SELECT 
    MAU.MonthLogin, 
    SUM(MAU.ActiveUsers) OVER (ORDER BY MAU.MonthLogin) [Count of Active Users]
FROM ...

    Note that the query can be simplified a little by using the short-circuit form of CASE, and mixing aggregation and window functions:

    SELECT 
    MonthLogin,
    COUNT(*) ActiveUsers,
    SUM(COUNT(*)) OVER(ORDER BY MonthLogin) [Count of Active Users]
FROM (
    SELECT 
        CASE MonthLogin
            WHEN'January'   THEN '2020-01-01'
            WHEN'February'  THEN '2020-02-01'
            WHEN'March'     THEN '2020-03-01'
            WHEN'April'     THEN '2020-04-01'
            WHEN'May'       THEN '2020-05-01'
            WHEN'June'      THEN '2020-06-01'
        END AS MonthLogin
    FROM #TEMPDistinctActiveUsersPerMonth
) t
GROUP BY MonthLogin
ORDER BY MonthLogin