1

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

1 Answer 1

3

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.)

1
  • thank you I knew it had to be easy but my skills are still quite lacking. take care.
    – Don
    Feb 4, 2016 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.