CODEWITHSUNDEEP
sql
samuel_C
samuel_C

Reputation: 497

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 the 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!

Upvotes: 1

Views: 121

Answers (6)

Ken White
Ken White

Reputation: 125651

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

Upvotes: 1

user839397
user839397

Reputation: 129

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

Upvotes: 1

StevieG
StevieG

Reputation: 8709

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

Upvotes: 2

Gerrat
Gerrat

Reputation: 29680

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

Upvotes: 1

Kerrek SB
Kerrek SB

Reputation: 476950

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.

Upvotes: 3

Nathan Hughes
Nathan Hughes

Reputation: 96385

Use GROUP BY:

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

Upvotes: 3

Related Questions