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?
We use system function now() to get the latest datetime value, and INTERVAL clause to calculate a date 7 days in the past.
How do I find last 30 days in SQL? SELECT * FROM product WHERE pdate >= DATEADD(day, -30, getdate()).
In this SQL Server example, we'll use the SUM Function and OVER to find the Running Total. Select in SQL Server Management Studio: Example 3: In this SQL Server example, we will use PARTITION BY with OVER to find the Running Total.
METHOD 1 : Using LIMIT clause in descending orderof specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending. Since Employee table has IDs, we will perform ORDER BY ID in our query.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With