CODEWITHSUNDEEP
phpmysqlsql
a53-416
a53-416

Reputation: 3935

In SQL, how do I get the awards total for each player

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).

Upvotes: 0

Views: 345

Answers (1)

codingbiz
codingbiz

Reputation: 26386

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

Upvotes: 4

Related Questions