SQL Query for Counting Records per Day

This SQL query will add up the record count per day based on a column called “Timestamp.”

Transact SQL

SELECT    DATEPART(YEAR, Timestamp) AS 'Year',
          DATEPART(MONTH, Timestamp) AS 'Month',
          DATEPART(DAY, Timestamp) AS 'Day',
          COUNT(*) AS 'Visits'
FROM      tblVisits
GROUP BY  DATEPART(DAY, Timestamp),
          DATEPART(MONTH, Timestamp),
          DATEPART(YEAR, Timestamp)
ORDER BY  'Year',
          'Month',
          'Day'

Results

The results of this query will appear as follows:

| Year | Month | Day | Visits |
|------|-------|-----|--------|
| 2011 | 2     | 7   | 46     |
| 2011 | 2     | 8   | 40     |
| 2011 | 2     | 9   | 37     |
| 2011 | 2     | 10  | 36     |
| 2011 | 2     | 11  | 41     |