I am using SQL Server 2008
I am trying to tally the wins and losses for any given bike. Each time a user votes, he casts a vote for one bike (1) and a vote against another bike (0).
My vote table looks like this:
VoteID --- BikeID ---- Vote
1 100 1
2 101 0
3 100 0
4 101 1
5 102 1
6 100 0
7 102 0
8 101 1
I want my results to look like this when I run a query for a specific bike
Wins -- Losses
5 6
Right now, my results look like this:
Wins --- Losses
5 NULL
NULL 6
My query looks like this:
SELECT SUM(CASE WHEN Vote = 1 THEN 1 END) AS Wins,
SUM(CASE WHEN Vote = 0 THEN 1 END) AS Losses
FROM Votes
WHERE BikeID = 101
GROUP BY Vote
What do I need to do to get the results on one line?
A CASE WHEN expression is often used with a SUM() function in more complex reports, which can be quite challenging for beginners. Even though you're probably used to using the SUM() function for summing values, it can also be used for counting. This example will help you understand the concept better.
CASE statement in SQL and aggregate functions Aggregate functions in SQL Server perform calculations and return a single value. Examples of aggregate functions are MIN, MAX, COUNT, ABG and CHECKSUM. For this purpose, we use the COUNT aggregate function in SQL Server.
The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well.
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table.
SELECT SUM(CASE WHEN Vote = 1 THEN 1 ELSE 0 END) AS Wins,
SUM(CASE WHEN Vote = 0 THEN 1 ELSE 0 END) AS Losses
FROM Votes
WHERE BikeID = 101
The problem is your case statements did not cover all conditions, and so were returning null for the cases where they did not account for.
Also, you did not need the group by vote, since you aren't actually selecting the vote outside the aggregates.
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