CODEWITHSUNDEEP
sqlsql-server
Martin Muldoon
Martin Muldoon

Reputation: 3428

How to GROUP BY DATETIME column in SQL

I have a table that tracks when a user scans a logo. It tracks the DATETIME of the scan and also the points awarded to the user for the scan (between 1 and 5)

I am trying to calculate the average points awarded per day for the given week. The challenge I have is when I use GROUP BY I'm getting back every scan because DATETIME value is by the second which can't be grouped.

SET DATEFIRST 1 -- Beginning of week is Monday
SELECT SUM(Points) AS Points, DateTime
FROM SmartTappScanLog
WHERE DateTime >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) 
  AND DateTime <  dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))
GROUP BY DATETIME

I've tried several things including:

GROUP BY CAST(DATETIME As DATE)

Upvotes: 1

Views: 60

Answers (1)

apomene
apomene

Reputation: 14389

You can use DATEPART(), like:

SELECT SUM(Points) AS Points, MIN(DateTime) as  DateTime
FROM SmartTappScanLog
WHERE DateTime >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) 
  AND DateTime <  dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))
GROUP BY DATEPART(day,DATETIME)

Upvotes: 1

Related Questions