Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Counting values in time period.

Counting values in time period.

Counting values in time period.


I have a table with two fields. procudts and DateTime.
how can I count the number of procudts on each hour ?
from 6:00 AM until 6:00 PM for example.

Table 1
A              2009-10-20 6.00.01
A              2009-10-20 6.10.10
B              2009-10-20 6.30.31
C              2009-10-20 7.00.41
C              2009-10-20 7.10.21
B              2009-10-20 7.35.31
B              2009-10-20 7.50.05
A              2009-10-20 8.00.20
A              2009-10-20 8.24.04
B              2009-10-20 9.04.02

Output is like this table:

---TimePriod----|---Product Count----
6-7                   3
7-8                   4
8-9                   2

instead of this time period format, I can assign simple numbers az a index.

Thank you so much,

RE: Counting values in time period.

i must ask you once again, which database system is this? sql server?

because you posted in the ANSI SQL forum

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

RE: Counting values in time period.

I'm using MS Access SQL and ADO connection in Delphi.

RE: Counting values in time period.

Date/time handling is one of the worst areas when it comes to ANSI/ISO compliance - a lot of DBMS products are NOT compliant, they have their own vendor specific handling.

Make sure your DBMS has implemented ANSI SQL date/time before asking in this forum. (Otherwise go to a product specific forum.)

RE: Counting values in time period.

Thank you for your answer.
Instead of Date/Time, I can use seconde also.
I access to the unix_seconde fields, which is absolute seconde.
I want to count products in the priode of 3600 secode.

Thanks again.


RE: Counting values in time period.

I think this would be close. It is just missing the sorting by date.

Hour([datetime]) is going to give you your hour blocks. Then just group it.

SELECT Hour([datetime]) AS Hour, Count(Table1.pro) AS CountOfpro
FROM Table1
GROUP BY Hour([datetime])
ORDER BY Hour([datetime]);


RE: Counting values in time period.

SELECT Format([datetime],"Short Date") AS Shortdate, Hour([dt]) AS [datetime], Count(Table1.pro) AS CountOfpro
FROM Table1
GROUP BY Format([datetime],"Short Date"), Hour([dt])
ORDER BY Hour([datetime]);

This adds in the date


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close