I'm trying to count or get the sum of values that match other values in a different table

Question

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 27-Apr-18 12:40pm

Commish13

Updated 1-May-18 10:47am

Wendelius

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2018-05-01T10:49:00

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.