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.
PK_User, uniqueidentifier ID, bigint Username, nvarchar(128) CreationTimestamp, datetime
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?