SQL: Virtual Grouping?

December 13, 2005 11:42 AM

SQL: Virtual Grouping?

I'd like to get a count of transactions organized by the hour, so that I can determine which time period in the day is the busiest. I can get the count for a single hour with this:

Select Count(Distinct TimeStamp) From Transactions Where TimeStamp Like '00:%'

Our timestamps go from 00:00:00 to 23:60:60.

So, what I want to do is consolidate a long list of these queries into something more efficient and versatile, if possible a single query:

Select Count(Distinct TimeStamp) From Transactions Where TimeStamp Like '00:%'
Select Count(Distinct TimeStamp) From Transactions Where TimeStamp Like '01:%'
...
Select Count(Distinct TimeStamp) From Transactions Where TimeStamp Like '22:%'
Select Count(Distinct TimeStamp) From Transactions Where TimeStamp Like '23:%'

What are some good ways to do this? Many thanks!

posted by odinsdream to computers & internet (9 comments total)

Er, the above should be Count(Distinct RecordID) in each line, sorry about that.
posted by odinsdream at 11:42 AM on December 13, 2005


select
count(left(timestamp, 2)), left(timestamp, 2)
from transactions
group by left(timestamp, 2)

posted by ldenneau at 11:53 AM on December 13, 2005

select datepart(hh,timestamp), count(distinct recordid)
from transactions
group by datepart(hh)

or better yet, count(1) rather than count(distinct recordid) if you're just counting the number of records.
posted by ltdan at 1:03 PM on December 13, 2005

whoops...messed up the 'group by'. should be 'group by datepart(hh,timestamp).
posted by ltdan at 1:04 PM on December 13, 2005

select datepart(hh, timestamp) as Hour, count(*) as Count
from transactions
group by datepart(hh, timestamp)
order by datepart(hh, timestamp)
posted by SNACKeR at 1:32 PM on December 13, 2005

itdan, there could be several transactions with the same RecordID, which should be treated as one "count" in the total. At least, that's what I was trying to accomidate with Count(Distinct RecordID).

Thanks everyone, I'll try these out in the morning.
posted by odinsdream at 1:40 PM on December 13, 2005

It looks like the timestamp column isn't a date datatype, so I don't think datepart() will work. ldenneau's answer looks right, except for counting the duplicate records... your count(distinct RecordID) should fix it....

select count(distinct recordid), left(ts, 2)
from transactions
group by left(ts, 2)

posted by blue mustard at 2:10 PM on December 13, 2005

Oops, looks like I'm wrong about the datepart(). It's perfectly happy with expressions like '00:00:00'.
posted by blue mustard at 2:18 PM on December 13, 2005

it probably does a cast.
posted by andrew cooke at 2:29 PM on December 13, 2005