CODEWITHSUNDEEP
sqlgroupingms-access-2013querying
perspiringprogrammer
perspiringprogrammer

Reputation: 11

How to query for grouped results in Access, based on count of time and day of week?

I have a table in an Access 2013 database, which has many rows of incidents, each of which has a "time" column and a "day_of_week" column. I need to query for a result set that shows a count of incidents during hourly time ranges from 6:00am-5:00pm, per day of the week. I can get a total count of incidents per time range like so:

SELECT "6:00AM - 6:59AM" AS [Time Range],COUNT(time) AS [Count]
FROM Incidents 
WHERE time >= #6:00:00 AM# AND time <=#6:59:00 AM#
UNION
SELECT "7:00AM - 7:59AM" AS [Time Range],COUNT(time) AS [Count]
FROM Incidents 
WHERE time >= #7:00:00 AM# AND time <=#7:59:00 AM#
UNION
...

So on, and so forth. It is a lengthy query, but it gets the task done. However, as stated previously, I need to drill down further to figure out how many incidents occurred specifically during each hourly time frame, per day of the week. So, I need to have a column for "time", and then an additional column for each day of the week. I know this will involve some grouping, but I am not certain what must be done.

This is my first post here. Hopefully I followed the rules.

Thanks

Upvotes: 0

Views: 190

Answers (1)

John Bingham
John Bingham

Reputation: 2006

This query should do what you need, except that it will only show hour as an hour number, rather than the expanded description your query fragment above shows:

select day_of_week, datepart("h",[time]) as hour_num, count(*)
from incidents
group by day_of_week, datepart("h",[time])

hope this helps

Upvotes: 1

Related Questions