I have two tables, one called Players and one called Awards. End-users give an "award" to a player, and players can receive the same award multiple times.
Awards Table
----+------------------------------------------------------------------------+
| ID | name | player_id |
+----+-----------------------------------------------------------------------+
| 1 | Free-throw Excerpt | 1 |
| 2 | Free-throw Excerpt | 6 |
| 3 | Top Earner | 1 |
| 4 | Top Player | 5 |
| 5 | Free-throw Excerpt | 1 |
| 6 | Free-throw Excerpt | 1 |
| 7 | Top Earner | 1 |
| 8 | Top Player | 1 |
...
+----+-----------------------------------------------------------------------
`Players Table`
----+------------------------------------------------------------------------+
| ID | name |
+----+-----------------------------------------------------------------------+
| 1 | Player A |
| 2 | Player B |
| 3 | Player C |
| 4 | Player D
... |
+----+-----------------------------------------------------------------------
In my app, each player has a page, and on that page, I want to display all the awards that the player has won. For example, for Player B's page:
Player A Stats:
I can query the the awards table to get all the awards given to Player A, but I'm stuck here and don't know where to go. Do I need to then do a COUNT(*) to get the number of each award received? (1 for Top Player, 3 for Free Throw Excerpt).
You are right. Count with Group By will do it
Select a.Name, Count(*) AS Total From Awards a
Where a.PlayerID = 1
Group By a.Name
Order By Count(*) DESC