How to count the number of occurrences of an ID from a different table
I'm making an access database on UFC fights. I have a fightschedule table
that has the IDs of each fighter for each fight and the winner of the
fights. I'm trying to make a query that lists every fighter that fought
and lists how many wins each one had.
This is an example of my fightSchedule table
ID Fighter1 Fighter2 Weight Class Date Winner
A 205 215 Light Heavyweight 8/14/2013 205
B 206 212 Welterweight 8/15/2013 212
C 207 218 Middleweight 8/14/2013 207
D 208 209 Heavyweight 8/14/2013 209
So for for example I want a query that would look something like this:
Fighter # of Wins
205 1
206 0
207 1
208 0
209 1
212 1
215 0
218 0
I don't know much about what would go into doing this at all. I know how
to use the count function but no idea how to use it like I want in this
example.
I found this on W3 Schools but I don't know if this is the right one to
use or how to use it.
SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name
Any help would be appreciated. Thank you!
No comments:
Post a Comment