SQL查询到本周的每一天的SUM（*）

I have an app that enables users to scan a logo and win "points". They can scan more than once per day. I'm trying to write a query that returns the total points for each day of this week. Thus far, I'm able to return all records for each day of the week. The challenge is how to sum total points for each day. Here is what I have thus far:

我有一个应用程序，使用户可以扫描徽标并赢得“积分”。他们每天可以扫描一次以上。我正在尝试编写一个查询，返回本周每一天的总积分。到目前为止，我能够返回一周中每一天的所有记录。挑战在于如何总计每天的总积分。这是我到目前为止：

SET DATEFIRST 1 -- Beginning of week is Monday
SELECT ScanID, UserID, DateTime, BeerID, BrewerID, Points 
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

So if there are two scans Monday worth 2 and 5 Points, I want to return 7 for Monday. Thanks.

因此，如果周一有两次扫描，分别为2点和5点，我希望周一返回7。谢谢。

1 个解决方案

#1

You need an aggregation. Something like this will get one row per day in the data:

你需要一个聚合。像这样的东西每天会在数据中获得一行：

SELECT CAST(Datetime as date) as date, UserID, BeerID, SUM(Points) as points
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 CAST(Datetime as date), UserID, BeerID
ORDER BY MIN(DateTime) ASC;

If you want the day of the week in the output, use datepart() or datename() to get it.

如果您想要输出中的星期几，请使用datepart（）或datename（）来获取它。

#1