How do I get a count of events each day with SQL?
- by upl8
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.
