CODEWITHSUNDEEP
sqlsql-server
Martin Muldoon
Martin Muldoon

Reputation: 3428

SQL - How to get record count for each day of week

I have a query that returns all records for the week which starts on Monday. I'm trying to get the record count for each day of the week with no success.

The Original Query:

SET DATEFIRST 1 -- Beginning of week is Monday
SELECT DateTime, ScanPoints, PostPoints 
FROM SmartTappScanLog
WHERE DateTime >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) 
  AND DateTime <  dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))
  AND UserID = '1' AND BeerID = '3'
  ORDER BY DateTime ASC

I've tried several things including Count(DateTime) with a Group By:

SET DATEFIRST 1 -- Beginning of week is Monday
SELECT Count(DateTime), ScanPoints, PostPoints 
FROM SmartTappScanLog
WHERE DateTime >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) 
  AND DateTime <  dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))
  AND UserID = '1' AND BeerID = '3'
  ORDER BY DateTime ASC
  GROUP BY DateTime

The error indicates there is a syntax error near GROUP.

Upvotes: 0

Views: 8236

Answers (3)

Wes H
Wes H

Reputation: 4439

Your query has a few issues,

  1. GROUP BY must come before ORDER BY.

  2. Non-aggregated columns selected must also be in the GROUP BY clause 

    SELECT Count(DateTime), ScanPoints, PostPoints... 
GROUP BY ScanPoints, PostPoints

  3. I assume your column DateTime actually contains a date and time. Grouping By this will give you a record for each unique date/time value in your data. Since you're wanting daily data, you should strip off the time by converting it to a date. By the way, DateTime is a data type and a terrible name for a column.

  4. I prefer not to use DATEFIRST as it does not affect all of the date functions equally. If you later use a datediff with weeks, the results may be wrong.

My company also uses Monday as the start of the week. By calculating the shifted first day of the week, you can change which week a Sunday belongs to.

CAST(DATEADD( DAY, 
             (DATEPART( WEEKDAY, [YourDate] ) + 5) % 7 * -1,
             [YourDate] ) 
    AS DATE) AS FirstDayOfWeekIsMonday

Using the FirstDayOfWeekIsMonday column for your week filtering/grouping will ensure that the Sunday is associated with the prior Monday.

As an added bonus, this expression is SARGable so indexes on YourDate can still be used.

Upvotes: 2

Santhosh
Santhosh

Reputation: 142

Group by should appear before Order by.

Your Query should be build as below:

SET DATEFIRST 1 -- Beginning of week is Monday

SELECT Count(DateTime), ScanPoints, PostPoints 
FROM SmartTappScanLog  
WHERE DateTime >= 
       dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) 
    AND DateTime <  
       dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))
    AND UserID = '1' AND BeerID = '3'
GROUP BY DateTime
ORDER BY DateTime ASC

Upvotes: 0

RegBes
RegBes

Reputation: 569

Give this a try ( Not tested)

SET DATEFIRST 1 -- Beginning of week is Monday
SELECT datepart(dw, DateTime) as Day, ScanPoints, PostPoints, Count(DateTime) as Recs,
FROM SmartTappScanLog
WHERE DateTime >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) 
  AND DateTime <  dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))
  AND UserID = '1' AND BeerID = '3'
  GROUP BY datepart(dw, DateTime) , ScanPoints, PostPoints
  ORDER BY datepart(dw, DateTime) , ScanPoints, PostPoints

Upvotes: 0

Related Questions