Click here to Skip to main content
15,113,057 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm trying to make a Football pool where the user picks the winner of each game. Then I would like to display users picks and the word Won or Lost next to the users picks. I also would like to display the number of wins each user has.

I have the first part working but I'm trying to count the wins in each row.

I have 2 tables.

1st Table: UserPicks
Columns: FullName, Week, Game_1, Game_2, Game_3, Game_4, Game_5

2nd Table: WeeklyResults - Only name of teams that won
Columns: Week, GameResults_1, GameResults_2, GameResults_3, GameResults_4, GameResults_5

Thanks for any help you give...

What I have tried:

This displays a Win or Lost. This is working good

SQL
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 'Win' ELSE 'Lost' END) AS GameResult_1,
       (CASE WHEN UserPicks.Game_2 = WeeklyResults.GameResults_2 THEN 'Win' ELSE 'Lost' END) AS GameResult_2,
       (CASE WHEN UserPicks.Game_3 = WeeklyResults.GameResults_3 THEN 'Win' ELSE 'Lost' END) AS GameResult_3,
       (CASE WHEN UserPicks.Game_4 = WeeklyResults.GameResults_4 THEN 'Win' ELSE 'Lost' END) AS GameResult_4,
	   (CASE WHEN UserPicks.Game_5 = WeeklyResults.GameResults_5 THEN 'Win' ELSE 'Lost' END) AS GameResult_5
 FROM UserPicks
 JOIN WeeklyResults ON UserPicks.Week = WeeklyResults.Week
WHERE WeeklyResults.Week = 'Week1'

This is my code to try and count the wins:<br> But it's not working correctly
SQL
SELECT  FullName, SUM(CASE WHEN UserPicks.Game_1 = WeeklyResults.GameResults_1 THEN 1
			               WHEN UserPicks.Game_2 = WeeklyResults.GameResults_2 THEN 1
			               WHEN UserPicks.Game_3 = WeeklyResults.GameResults_3 THEN 1
				           WHEN UserPicks.Game_4 = WeeklyResults.GameResults_4 THEN 1
				           WHEN UserPicks.Game_5 = WeeklyResults.GameResults_5 THEN 1 END) AS [Count of Wins]
  FROM UserPicks
  JOIN WeeklyResults ON UserPicks.Week = WeeklyResults.Week 
  GROUP BY FullName
Posted
Updated 1-May-18 11:47am
v3
Comments
Wendelius 28-Apr-18 0:25am
   
Please explain in more detail how it's not working correctly. What was the output and what was the expected output
Rajat-Indiandotnet 30-Apr-18 9:54am
   
Could you please add same data tables?

1 solution

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.
SQL
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:
SQL
-- Create the table and insert sample data.  
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)


-- Unpivot the table.  
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.
   
v2
Comments
Commish13 1-May-18 19:22pm
   
Thanks, With your code I was able to get the results in needed. I am having trouble displaying the Total or Points on my site. I'm playing with it now but if you have any suggestions it would be appreciated.

Thanks for you help with the above code

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900