Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to tally wins and losses using SUM and CASE?

Tags:

sql

tsql

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?

like image 564
Evik James Avatar asked Jan 07 '12 18:01

Evik James


People also ask

Can I use SUM in CASE statement?

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.

Can we use aggregate function in case statement?

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.

Can we use group by in case?

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.

How do I SUM individual rows in SQL?

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.


1 Answers

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.

like image 67
Jake Feasel Avatar answered Nov 09 '22 12:11

Jake Feasel