Well, you've got your answer here:
Sql CASE statement not working[
^], but you won't even learn how to optimise your database... Sad...
You've got 2 options:
1. Sum up every game (simple)
2. unpivot data and sum each (more complicated)
As to 1.
SELECT FullName, Game_1, Game_2, Game_3, Game_4, Game_5, GameResult_1 + GameResult_2 + GameResult_3 + GameResult_4 + GameResult_5 AS TotalPoints
FROM (
SELECT UserPicks.FullName, UserPicks.Game_1, UserPicks.Game_2, UserPicks.Game_3, UserPicks.Game_4, UserPicks.Game_5,
(CASE WHEN UserPicks.Game_1 = WeeklyResults.GameResults_1 THEN 1 ELSE 0 END) AS GameResult_1,
(CASE WHEN UserPicks.Game_2 = WeeklyResults.GameResults_2 THEN 1 ELSE 0 END) AS GameResult_2,
(CASE WHEN UserPicks.Game_3 = WeeklyResults.GameResults_3 THEN 1 ELSE 0 END) AS GameResult_3,
(CASE WHEN UserPicks.Game_4 = WeeklyResults.GameResults_4 THEN 1 ELSE 0 END) AS GameResult_4,
(CASE WHEN UserPicks.Game_5 = WeeklyResults.GameResults_5 THEN 1 ELSE 0 END) AS GameResult_5
FROM UserPicks
JOIN WeeklyResults ON UserPicks.Week = WeeklyResults.Week
WHERE WeeklyResults.Week = 'Week1' ) AS CommonData
As to 2.
Assuming that your query returns something like this:
FullName Game1 Game2 Game3 Game4 Game5 GameResult1 GameResult2 GameResult3 GameResult4 GameResult5
User1 Game1 Game2 Game3 Game4 Game5 1 1 0 1 0
User1 Game1 Game2 Game3 Game4 Game5 1 0 1 1 0
User1 Game1 Game2 Game3 Game4 Game5 0 1 1 1 1
User1 Game1 Game2 Game3 Game4 Game5 1 1 0 0 0
User1 Game1 Game2 Game3 Game4 Game5 0 1 0 1 0
use:
DECLARE @commondata TABLE (FullName VARCHAR(30), Game1 VARCHAR(30), Game2 VARCHAR(30), Game3 VARCHAR(30), Game4 VARCHAR(30), Game5 VARCHAR(30),
GameResult1 INT, GameResult2 INT, GameResult3 INT, GameResult4 INT, GameResult5 INT)
INSERT INTO @commondata (FullName, Game1, Game2 , Game3, Game4, Game5,
GameResult1, GameResult2, GameResult3, GameResult4, GameResult5)
VALUES ('User1', 'Game1', 'Game2', 'Game3', 'Game4', 'Game5', 1,1,0,1,0),
('User1', 'Game1', 'Game2', 'Game3', 'Game4', 'Game5', 1,0,1,1,0),
('User1', 'Game1', 'Game2', 'Game3', 'Game4', 'Game5', 0,1,1,1,1),
('User1', 'Game1', 'Game2', 'Game3', 'Game4', 'Game5', 1,1,0,0,0),
('User1', 'Game1', 'Game2', 'Game3', 'Game4', 'Game5', 0,1,0,1,0)
SELECT FullName, Game, SUM(Points) As Total
FROM
(SELECT *
FROM @commondata) pvt
UNPIVOT
(Points FOR Game IN
(GameResult1, GameResult2, GameResult3, GameResult4, GameResult5)
)AS unpvt
GROUP BY FullName, Game
Result:
FullName Game Total
User1 GameResult1 3
User1 GameResult2 4
User1 GameResult3 2
User1 GameResult4 4
User1 GameResult5 1
In case you want only
FullName
and total points, remove
Game
column from
SELECT
list and
GROUP BY
statement.