CODEWITHSUNDEEP
mysqldatabasejoinsubquerywhere-clause
Praneeth Vasarla
Praneeth Vasarla

Reputation: 123

How to get a specific column value from current row in mysql?

How can I get the value of a specific column in the current row? Let's say we have a table "games" with columns "gameid" and "game_type". Each game has multiple winners (one user can win any number of times). Now let's assume we have another table "game_winners" which keeps track of all the game-winners and has "gameid", "userid". What I want to achieve is get the number of unique winners in a particular game. The query I need would look something like this:

select gameid, game_type, (
  select count(distinct(userid)) 
  from games 
  join winners 
  where gameid = {gameid of current row}
) as game_winners 
from games 
join winners

I'm not sure how to get the gameid from the current row and pass it into the where clause.

Any help is very much appreciated.

Upvotes: 1

Views: 2091

Answers (2)

Vivek Gondhiya
Vivek Gondhiya

Reputation: 129

This would give you expected result:

SELECT
    g.gameid,
    g.game_type,
    count(DISTINCT gw.user_id) as game_winners
FROM
    `game` g
    LEFT JOIN winners gw ON gw.game_id = g.gameid
GROUP BY
    g.gameid;

If you want to get result of single game, just add: (replace '1' with your ID)

WHERE g.gameid = 1;

Upvotes: 0

sticky bit
sticky bit

Reputation: 37487

You can either use a correlated subquery aggregating:

SELECT g.gameid,
       g.game_type,
       (SELECT count(DISTINCT w.userid)
               FROM winners w
               WHERE w.gameid = g.gameid) AS game_winners
       FROM games g;

Or join and then aggregate:

SELECT g.gameid,
       g.game_type,
       count(DISTINCT w.userid) AS game_winners
       FROM games g
            LEFT JOIN winners w
                      ON w.gameid = g.gameid
       GROUP BY g.gameid,
                g.game_type;

And BTW, DISTINCT is not a function, the parenthesis aren't needed.

Upvotes: 1

Related Questions