Marv174 Marv174 - 2 months ago 7
SQL Question

Can I get the sum of the day, the week and the month in 1 MS SQL query?

When I have a table with the columns


  • user

  • points

  • createdAt



Can I get all points for each user of the day, the week and the month in 1 MS SQL query?
So that the result looks something like this:



|user |pointsDay|pointsWeek|pointsMonth|
|user1| 12 | 50 | 200 |
|user2| 10 | 60 | 222 |


...

K.K K.K
Answer

This help you,

    SELECT [USER],
        SUM(POINTS) POINTSMONTH,
        SUM(CASE WHEN CREATEDATE>DATEADD(DAY,-7,GETDATE()) THEN POINTS ELSE 0 END) POINTSWEEK,
        SUM(CASE WHEN CREATEDATE>DATEADD(DAY,-1,GETDATE()) THEN POINTS ELSE 0 END) POINTSDAY
    FROM USERS
    WHERE CREATEDATE> DATEADD(DAY,-30,GETDATE())
    GROUP BY [USER]
Comments