Return only Unique values from DB and count

[eaglesfan17]

Alright, I have a table in my database named 'game_passing'. In that table, there are two fields, one named 'game_id' and one named 'team'. For every 'team' I need to return the number of 'game_id's then count them. The reason being is that I need to produce a variable called $total_games_played (or somiliar).

Basically, I need to return the number of unique 'game_id' values and then add them together (not the actual data, just the number of), But I'm lost on how to return and count unique results.

Any clue on how I can accomplish this?

(modifying the database is not an option)

[toplay]

Try something like this:

SELECT  team, COUNT(game_id) AS total_games_played FROM game_passing GROUP BY team ORDER BY total_games_played DESC

That will also sort it by the number of games each team has from highest to lowest number. Change DESC to ASC if you want it from lowest number of games to most number of games.

This is untested and may contain syntax errors. But if you get any just post what they are here again. It helps to know what MySQL version you have.

[eaglesfan17]

That is beautiful...BUT it is counting every instance of game_id and I need it to count only the instances of game_id that are unique.

[toplay]

Try adding the keyword DISTINCT and see:

SELECT team, COUNT(DISTINCT game_id) AS total_games_played 
FROM game_passing 
GROUP BY team 
ORDER BY total_games_played DESC

See manual page: http://dev.mysql.com/doc/mysql/en/group-by-functions.html#id2949669

🙂

[eaglesfan17]

Perfect 😃