Loading

Tuesday, September 1, 2009

SQL Server count items per day or per hour

Here are a couple of quick SQL statements to return counts based on entries or items or transactions per day or per hour. You can tailor as desired.

-- Count items per day
SELECT Year, Month, Day, COUNT(Day) AS "Items per Day"
FROM
(
SELECT DATEPART(YEAR,CreatedOn) Year, DATEPART(MONTH,CreatedOn) Month,
DATEPART(DAY,CreatedOn) Day
FROM tableName
) temp
group by Year, Month, Day
order by Year desc, Month desc, Day desc
------------------------------------------------
-- Count items per hour
SELECT Year, Month, Day, Hour, COUNT(Hour) AS "Items per Hour"
FROM
(
SELECT DATEPART(YEAR,CreatedOn) Year, DATEPART(MONTH,CreatedOn) Month,
DATEPART(DAY,CreatedOn) Day, DATEPART(HOUR, CreatedOn) Hour
FROM TableName
) temp
group by Year, Month, Day, Hour
order by Year desc, Month desc, Day desc, Hour desc

Keywords:
sql server something per day
sql count per hour
sql count per month
sql count time of day
"sql server" count
sql server day count
count per hour in sql
count records per hour per day sql
day,hour,minute format in sqlserver
entries per day sql select

4 comments:

  1. This is petty awesome.. Helped alot.. Thanks dude!

    ReplyDelete
  2. This was a great help - thanks!!!

    ReplyDelete
  3. nice, for have it work in MYSQL change cetral part to

    SELECT date(time) as date, year(Time) Year, MONTH(Time) Month,
    DAY(Time) Day

    ReplyDelete
  4. That is a great query. I realize this post is very old..... although I was hoping to have 0 count besides the hours without an entry. How would we do that?

    ReplyDelete