SQL query to count entries in multiple columns

Question

I have a database with two tables, tblPlayers and tblMatches.

tblPlayers contains the following fields: playerID, playerName.

tblMatches contains matchID, scorer1ID, scorer2ID, scorer3ID.

I need to run a query to count the total number of goals scored by a player, and return it with their playerID.

I tried the following:

SELECT
tblPlayers.playerID, tblPlayers.playerName, count(*) AS goals
FROM
tblPlayers, matches
WHERE
(tblPlayers.playerID = tblMatches.scorer1ID
OR
tblPlayers.playerID = tblMatches.scorer2ID
OR
tblPlayers.playerID = tblMatches.goalscorer3ID)
GROUP BY tblPlayers.playerID
ORDER BY goals DESC

However, this isn't working as required, as the count function doesn't allow for the fact that a player can score more than one goal in a game (e.g. if a player scores three goals his ID appears in the scorer1ID field, scorer2ID field and scorer3ID field, but the count just returns a value of 1, when it needs to be 3).

Hope that makes sense, I've been tearing my hair out for the last few days trying to solve this! Any help greatly appreciated.

Did you set up the tables this way? I think there are more efficient ways to do this. — zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
– zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz, Commented Apr 5, 2012 at 21:41

Michael Fredrickson · Accepted Answer · 2012-04-05 21:41:07Z

2

SELECT
    playerID,
    playerName,
    SUM(goals) AS goals
FROM 
(
    SELECT p.playerID, p.playerName, COUNT(*) AS goals
    FROM 
        tblPlayers p
        JOIN tblMatches m ON m.scorer1ID = p.playerID
    GROUP BY p.playerID, p.playerName

    UNION ALL

    SELECT p.playerID, p.playerName, COUNT(*) AS goals
    FROM 
        tblPlayers p
        JOIN tblMatches m ON m.scorer2ID = p.playerID
    GROUP BY p.playerID, p.playerName

    UNION ALL

    SELECT p.playerID, p.playerName, COUNT(*) AS goals
    FROM 
        tblPlayers p
        JOIN tblMatches m ON m.scorer3ID = p.playerID
    GROUP BY p.playerID, p.playerName
) sub
GROUP BY playerID, playerName

Or, here's a query that does it in one pass:

SELECT 
    p.playerID, 
    p.playerName, 
    SUM (
        CASE WHEN p.playerID = m.scorer1ID THEN 1 ELSE 0 END +
        CASE WHEN p.playerID = m.scorer2ID THEN 1 ELSE 0 END +
        CASE WHEN p.playerID = m.scorer3ID THEN 1 ELSE 0 END
    ) AS goals
FROM 
    tblPlayers p
    JOIN tblMatches m ON p.playerID IN (m.scorer1ID, m.scorer2ID, m.scorer3ID)
GROUP BY p.playerID, p.playerName

answered Apr 5, 2012 at 21:41

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Conrad Frix Over a year ago

+1 and thanks for the upvote on my now deleted answer. I'm sure you were working it out before you saw mine. Nice use of IN on the JOIN btw.

The Seldom Seen Kid Over a year ago

That's brilliant, many thanks for your help. Really appreciate it.

Elroy Flynn · Accepted Answer · 2012-04-05 21:42:22Z

0

That is a terrible table design, but if you are stuck with it then the following should do:

SELECT
tblPlayers.playerID, tblPlayers.playerName,  
(select count(*) from tblMatches m where p.playerId = m.Scoreer1Id)
+
(select count(*) from tblMatches m where p.playerId = m.Scoreer2Id)
+
(select count(*) from tblMatches m where p.playerId = m.Scoreer3Id)
from tblPlayers p

answered Apr 5, 2012 at 21:42

Elroy Flynn

3,2481 gold badge28 silver badges37 bronze badges

Comments

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz · Accepted Answer · 2012-04-05 21:44:55Z

0

Try this. It uses embedded select statements to sum the number of times a player shows up in each columnn

SELECT tblPlayers.playerID, tblPlayers.playerName, ((SELECT COUNT(*) FROM tblPlayers, matches WHERE tblPlayers.playerID = tblMatches.scorer1ID) + (SELECT COUNT(*) FROM tblPlayers, matches WHERE tblPlayers.playerID = tblMatches.scorer2ID) + (SELECT COUNT(*) FROM tblPlayers, matches WHERE tblPlayers.playerID = tblMatches.scorer2ID)) As [GoalsScored] 
FROM tblPlayers
WHERE
(tblPlayers.playerID = tblMatches.scorer1ID
OR
tblPlayers.playerID = tblMatches.scorer2ID
OR
tblPlayers.playerID = tblMatches.goalscorer3ID)
GROUP BY tblPlayers.playerID
ORDER BY goals DESC

answered Apr 5, 2012 at 21:44

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

5,7798 gold badges58 silver badges72 bronze badges

Collectives™ on Stack Overflow

SQL query to count entries in multiple columns

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related