mysql query - multiple counts using left join and where clause

Question

1.00/5 (1 vote)

See more:

I'm currently trying to get the following data:

UserName, UserImageURL, Total Games Played, Games Completed, Games Lost, Average Won (as percentage) and Points of the user

And as well another set of data:

User Statistics data such as:

Most Games Played on League: 23 - Monster Killers
Games Most Won On: 19/23 - Monster Killers
Games Most Lost On: 3/32 - Frog Racers
Your Game Winning Accuracy (total from all games) - 68% accuracy
Site Stats:

Most Games Played on League: 650 - Helicopter Run
Top Game Played: 1200 - Monster Killers
Whole site winning accuracy: 82%
I have the following Tables:

-User Table-

userID (int-pk), userName (varchar), userImageUrl (text)

-Games table-

gameId (int-pk), gameName (varchar), gameUserID (int), gameLeagueId (int), score1 (int), score2 (int), gameResultOut (0 or 1), gameWon (0 or 1)

-UserBalance table-

ubId(int-pk) userId (int) balance (int)

-League table- leagueId (int-pk) leagueName (varchar)

Just to give you a heads up on what's happening, when a user plays a game and chooses some results a row is inserted into the games table. Since the game is time based, when the results are out, there is a check that checks if there are any games which have that id and will update the gameResultOut to 1 and gameWon to 1 or 0 according to what the user had selected as a score.

I tried the following:

SQL

SELECT u.userID, u.userName, u.userImageUrl, l.leagueName , 
COUNT(g.gameId) AS predTotal, 
(SELECT COUNT(g.gameId) FROM games AS g WHERE g.gameResultOut = 1 AND g.gameWon = 1) AS gamesWon,
(SELECT COUNT(g.gameId) FROM games AS g WHERE g.gameResultOut = 1 AND g.gameWon = 0) AS gamesLost, 
ub.balance 
FROM games AS g
LEFT JOIN league AS l ON l.leagueId = g.gameLeagueId 
LEFT JOIN user AS u ON u.user_id = g.gameUserID 
LEFT JOIN user_balance AS ub ON ub.userId = u.userID 
WHERE l.leagueId = 4
GROUP BY u.userId 
ORDER BY ub.balance DESC

I can calculate easily the win percentage after the query so that's not a problem, but the result for the Wins and Lost are all the same and even when it comes to changing the leageId, the results are still the same which is not what I want.

Can anyone help?

Thanks & Regards, Necron8

Posted 11-Sep-13 3:55am

Necron8

Updated 11-Sep-13 5:06am

v2

Add a Solution

Comments

Maciej Los 11-Sep-13 17:49pm

Not clear...
Please, be more specific and provide more details (sample data and expected output).

Necron8 12-Sep-13 10:54am

Hi there guys, solved the first query:

SELECT u.userID, u.userName, u.userImageUrl, l.leagueName ,
COUNT(g.gameId) AS predTotal,
(SELECT COUNT(gw.gameId) FROM games AS gw WHERE gw.gameResultOut = 1 AND gw.gameWon = 1) AND gw.gameUserID = u.user_id AS gamesWon,
(SELECT COUNT(gl.gameId) FROM games AS gl WHERE gl.gameResultOut = 1 AND gl.gameWon = 0) AND gl.gameUserID = u.user_id AS gamesLost,
ub.balance
FROM games AS g
LEFT JOIN league AS l ON l.leagueId = g.gameLeagueId
LEFT JOIN user AS u ON u.user_id = g.gameUserID
LEFT JOIN user_balance AS ub ON ub.userId = u.userID
WHERE l.leagueId = 4
GROUP BY u.userId
ORDER BY ub.balance DESC

I forgot to change the names of the select to get the unique user predictions won/lost count.

As regarding to statistics, I'm trying to get the following result but I'm getting gamesWon column undefined:

Trying to get a ratio for the number of games won per user:

SELECT
g.gameName,
(SELECT COUNT(gw.gameId) FROM games gw WHERE g.gameResultOut = 1 AND g.gameWon = 1 AND gw.gameUserID = u.user_id) AS gamesWon,
COUNT(g.gameId) AS gamesPlayed,
(gamesWon / gamesPlayed) AS winRatio
FROM
games g
INNER JOIN user u ON u.user_id = g.gameUserID
GROUP BY g.gameId, u.user_id
ORDER BY winRatio DESC
LIMIT 1

Love 2 code 12-Sep-13 11:36am

Hi,
the column gamesplayed is unknown at the old level, you can use another subselect:

SELECT NAME, gamesWon, gamesPlayed,
(gamesWon / gamesPlayed) AS winRatio
FROM
(
SELECT
g.gameName AS Name,
(SELECT COUNT(gw.gameId) FROM games gw WHERE g.gameResultOut = 1 AND g.gameWon = 1 AND gw.gameUserID = u.user_id) AS gamesWon,
COUNT(g.gameId) AS gamesPlayed
FROM
games g
INNER JOIN user u ON u.user_id = g.gameUserID
GROUP BY g.gameId, u.user_id
ORDER BY winRatio DESC
LIMIT 1
) t

Necron8 13-Sep-13 4:48am

Minor modification to your query

the order by WinRation Desc and LMIT1 must be after the 't'

since the field is on the outer level of the query.

This isn't right though, since it gets each game played individually user (if i remove the limit)

Ex:
Game Name userName gamesWon gamesPlayed winRatio
Monster Hunt John 0 1 0
Monster Hunt John 1 1 1
Fishing Kate 1 1 1
Fishing John 1 1 1
Fishing John 1 1 1

Thanks to all those who are helping

Love 2 code 12-Sep-13 5:37am

Have you tried using an inner joins instead of the left joins?

Necron8 12-Sep-13 10:58am

For the first query, I had to name the select as a different table name since I had to retrieve each and every user prediction won / lost, so one is solved. I have commented regarding the user stats above but couldn't get it to work. Can you help me and see the above comment? Thanks!

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)