Help in writing an sql query

I am trying to write a query that would fetch the number of player in a sports team for every country in the table:

I have a table with the following fields

country | sports_team | player_name

What I want to display is a reulst similar to this

country | sports_team | number_of_players

i.e. I would like to loop through all th开发者_运维知识库e countries, and for every change in sports_team, I would like to record the nummber of players that are in the team.

Sample table:

country | sports_team | player_name
c1      |  teamA      |  name1
c1      |  teamA      |  name2
c1      |  teamB      |  name3
c2      |  teamC      |  name4
c2      |  teamC      |  name5

Sample results

country | sports_team | number_of_players
c1      | teamA       | 2
c1      | teamB       | 1
c2      | teamC       | 2

I am new to writing sql queries and I have no idea how to procede, any help will be appreciated!

Try this:

SELECT country, sports_team, COUNT(*) AS number_of_players
FROM yourtable
GROUP BY country, sports_team
ORDER BY country, sports_team;

Aggregate queries like COUNT are the key for this sort of data processing.

Use GROUP BY:

SELECT country, sports_team, COUNT(*)
FROM whatever_your_table_is    
GROUP BY country, sports_team

select 
  country,
  sports_team,
  count(*) number_of_players
from table
group by country, sports_team

select country, sports_team, count(*) from <your table> group by country, sports_team

No need to loop, just aggregate.

SELECT country, sports_team, COUNT(*)
FROM myTable
GROUP BY country, sports_team

The magic is in the COUNT(*) and the GROUP BY clause

You need to select the country and team and group by those columns to display the count. This will work for you:

SELECT 
  Country, Sports_Team, COUNT(player_name) AS number_of_players
FROM 
  YourTable
GROUP BY 
  Country, Sports_Team