CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Skatterbrainz
Skatterbrainz

Reputation: 1087

T-SQL Query / Count Rows by Partial Date Grouping

I have a table (in SQL Server 2008 R2, in case that matters), defined with the following rows. The DateAdded column is a SmallDateTime data type.

 ID    DateAdded
  1    2012-08-01 12:34:02
  2    2012-08-01 12:48:25
  3    2012-08-05 08:50:22
  4    2012-08-05 11:32:01
  5    2012-08-26 09:22:15 
  6    2012-08-26 13:42:02
  7    2012-08-27 08:22:12

What I need to do is count the rows that occur on the same YYYY/MM/DD value. So the results I need to obtain would look like this...

DateAdded   QTY
2012-08-01  2  
2012-08-05  2  
2012-08-26  2  
2012-08-27  1

I can't figure out the syntax/expression to get this to work. Can someone point me in the right direction? Thank you!

Upvotes: 1

Views: 1021

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

SELECT 
  DateAdded = DATEADD(DAY, DATEDIFF(DAY, 0, DateAdded), 0), 
  QTY = COUNT(*)
FROM dbo.tablename
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, DateAdded), 0);

Or as Marc rightly pointed out (I spent more time looking at the formatting botches than the tags):

SELECT 
  DateAdded = CONVERT(DATE, DateAdded), 
  QTY = COUNT(*)
FROM dbo.tablename
GROUP BY CONVERT(DATE, DateAdded);

Upvotes: 5

Related Questions