How to get the number of events every day using SQL?

Question

I have a table that looks like this:

Timestamp          Event   User
================   =====   =====
1/1/2010 1:00 PM   100     John
1/1/2010 1:00 PM   103     Mark
1/2/2010 2:00 PM   100     John
1/2/2010 2:05 PM   100     Bill
1/2/2010 2:10 PM   103     Frank

I want to write a query that shows the events for each day and the score for these events. Sort of:

Date       Event   EventCount
========   =====   ==========
1/1/2010   100     1
1/1/2010   103     1
1/2/2010   100     2
1/2/2010   103     1

The database is SQL Server Compact, so it does not support all the features of a full SQL Server. The request that I have written so far,

SELECT DATEADD(dd, DATEDIFF(dd, 0, Timestamp), 0) as Date, Event, Count(Event) as EventCount
FROM Log
GROUP BY Timestamp, Event

It almost works, but EventCount is always 1. How can I get SQL Server to return the correct values? All fields are required.

Answer 1

Change your goup to

GROUP BY DATEADD(dd, DATEDIFF(dd, 0, Timestamp), 0), Event