This post is part of a project to move my old reference material to my blog. Before 2012, when I accessed the same pieces of code or general information multiple times, I would write a quick HTML page for my own reference and put it on a personal site. Later, I published these pages online. Some of the pages still get used and now I want to make them available on my blog.
Photo by Daniel Chen
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 |