I am experiencing problems with formulating a (relatively) simple SQL Query (SQL Server 2012 is used). We have a database which counts something up for certain users. Therefore we have a really simple database structure consisting of two tables.
Table users
:
PK_User, uniqueidentifier
ID, bigint
Username, nvarchar(128)
CreationTimestamp, datetime
Table data
:
PK_Data, uniqueidentifier
FK_User, uniqueidentifier
FK_Reporter, uniqueidentifier
CreationTimestamp, datetime
I am currently working with the following SQL Statement:
SELECT u.Username, COUNT(d.FK_User) AS 'Count', CAST(FLOOR(CAST(d.CreationTimestamp AS float)) AS datetime) AS 'Date'
FROM data d INNER JOIN users u ON u.PK_User = d.FK_User
GROUP BY CAST(FLOOR(CAST(d.CreationTimestamp AS float)) AS datetime), u.Username
ORDER BY CAST(FLOOR(CAST(d.CreationTimestamp AS float)) AS datetime)
which delivers something like this:
User1 5 %Date1%
User2 3 %Date1%
User1 7 %Date2%
User2 1 %Date2%
So I am able to get the sum for each user for each specific day. I would like to sum this sum up, so that User1 gets for the second day 5+7=12 and User2 3+1=4.
How can I achieve this?
Best Answer
You need a "window" aggregate, i.e. an
OVER
clause in the aggregate. And because the query already has aGROUP BY
, the aggregate needed is theSUM()
over theCOUNT(d.FK_User)
you already have:ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
is not strictly needed. The default frame for window aggregates when there is anORDER BY
insideOVER ()
isRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
, which would produce the same results asROWS
but is likely to generate a suboptimal execution plan. See Aaron Bertrand's blog article Best approaches for running totals – updated for SQL Server 2012 for details."Count"
or square brackets[Count]
.