How do I get a count of events each day with 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 a count for those events. Something like:

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 doesn't support all the features of the full SQL Server. The query I have written so far is

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

This almost works, but EventCount is always 1. How can I get SQL Server to return the correct counts? All fields are mandatory.

Answer 1

Change your goup by to

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