Sql-server – get number of events per day from profiler trace

profilersql serversql server 2014

I am trying to do something that I thought would be simple but turns out it is not (for me).
I have 5 days of profiler trace files that I have imported to a single table.
I want to query the table and get the number of events per day.

I just want a simple result that looks something like this

StartTime count
2016-02-03 35600
2016-02-02 44000
2016-02-01 55055 etc...

so I found some examples but they don't seem to work.

select cast(StartTime as DATE) as StartTime, count(*) as [count]
from trace_table
group by StartTime

This is not working as I expected. It's bringing back over five million rows with a count of only small digits for a count number. I think I should get back five rows with a count for each day. If there is an easier way to accomplish this I am very interested.
Thanks,
Don

Best Answer

Try: 

GROUP BY cast(StartTime as DATE)
ORDER BY cast(StartTime as DATE);

Grouping by the raw, unconverted value means you are guaranteed to have a row for every unique StartTime. (And it probably makes sense to sort that way, otherwise your rows could come back in an arbitrary and unintuitive order.)

Related Question