How to query to get totals for last seven days?

Question

I am using SQL Server 2008.

I want to write a query that gives me total activity for a number of given days. Specifically, I want to count total votes per day for the last seven days.

My table looks like this:

 VoteID --- VoteDate --------------  Vote --- BikeID

 1          2012-01-01 08:24:25      1        1234 
 2          2012-01-01 08:24:25      0        5678
 3          2012-01-02 08:24:25      1        1289
 4          2012-01-03 08:24:25      0        1234
 5          2012-01-04 08:24:25      1        5645
 6          2012-01-05 08:24:25      0        1213
 7          2012-01-06 08:24:25      1        1234
 8          2012-01-07 08:24:25      0        1125

I need my results to look like this

VoteDate ---- Total
2012-01-01    5
2012-01-02    6
2012-01-03    7
2012-01-04    1
2012-01-05    3

My thought is that I have to do something like this:

SELECT    SUM(CASE WHEN Vote = 1 THEN 1 ELSE 0 END) AS Total
FROM      Votes
GROUP BY  VoteDate

This query doesn't work because it counts only votes that occurred (almost exactly) at the same time. Of course, I want to look only at a specific day. How do I make this happen?

Answer 1

Cast it as a date:

SELECT    
     cast(VoteDate as date) as VoteDate, 
     SUM(CASE WHEN Vote = 1 THEN 1 ELSE 0 END) AS Total
FROM      Votes
WHERE VoteDate between dateadd(day, -7, GETDATE()) and GETDATE()
GROUP BY  cast(VoteDate as date)

Your VoteDate column is a datetime, but you just want the date part of it. The easiest way to do that is to cast it as a date type. You can read more about SQL Server date types here.

And if your Vote column is either 1 or 0, you can just do sum(vote) as Total instead of doing the case statement.

Answer 2

SELECT SUM(Vote) As Total, YEAR(VoteDate),Month(VoteDate),Day(VoteDate)
FROM Votes
Group By YEAR(VoteDate),Month(VoteDate),Day(VoteDate)

Some SQL Server functions that may be of interest

Some MySQL functions that may be of interest