Databases
StackOverflow2013
Postsdbo
POmysql query - multiple counts using left join and where clause
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql query - multiple counts using left join and where clause
    primarykey
    data
    text
    <p>I'm currently trying to get the following data:</p> <p>UserName, UserImageURL, Total Games Played, Games Completed, Games Lost, Average Won (as percentage) and Points of the user</p> <p>And as well another set of data:</p> <p>User Statistics data such as:</p> <ul> <li>Most Games Played on League: 23 - Monster Killers</li> <li>Games Most Won On: 19/23 - Monster Killers</li> <li>Games Most Lost On: 3/32 - Frog Racers</li> <li>Your Game Winning Accuracy (total from all games) - 68% accuracy</li> </ul> <p>Site Stats:</p> <ul> <li>Most Games Played on League: 650 - Helicopter Run</li> <li>Top Game Played: 1200 - Monster Killers</li> <li>Whole site winning accuracy: 82%</li> </ul> <p>I have the following Tables:</p> <p>-User Table-</p> <p>userID (int-pk), userName (varchar), userImageUrl (text)</p> <p>-Games table-</p> <p>gameId (int-pk), gameName (varchar), gameUserID (int), gameLeagueId (int), score1 (int), score2 (int), gameResultOut (0 or 1), gameWon (0 or 1)</p> <p>-UserBalance table-</p> <p>ubId(int-pk) userId (int) balance (int)</p> <p>-League table- leagueId (int-pk) leagueName (varchar)</p> <p>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.</p> <p>I tried the following:</p> <pre><code>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 </code></pre> <p>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.</p> <p>Can anyone help?</p> <p>Thanks &amp; Regards, Necron</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNecron
    1. USNecron
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload