How to request a summary of the past seven days?

Question

I am using SQL Server 2008.

I want to write a query that gives me full activity for a few given days. In particular, I want to calculate the total number of votes per day over the past 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 should do something like this:

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

This query does not work because it only considers voices that occurred (almost exactly) at the same time. Of course, I want to look only at a certain day. How to do it?

Answer 1

Pass it as 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 its date part. The easiest way to do this is to use it as a date type. Learn more about SQL Server date types here .

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

Answer 2

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

Some features of SQL Server that may be of interest

Some MySQL functions that may be of interest