Help in writing an sql query

Question

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!

try reading just a small amount on SELECT statements. this should be on about page 2. — Randy
– Randy, Commented Jul 11, 2011 at 18:01

Kerrek SB · Accepted Answer · 2011-07-11 17:59:59Z

3

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.

answered Jul 11, 2011 at 17:59

Kerrek SB

481k96 gold badges904 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nathan Hughes · Accepted Answer · 2011-07-11 17:59:39Z

3

Use GROUP BY:

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

answered Jul 11, 2011 at 17:59

Nathan Hughes

96.7k21 gold badges193 silver badges288 bronze badges

Comments

StevieG · Accepted Answer · 2011-07-11 18:01:04Z

2

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

answered Jul 11, 2011 at 18:01

StevieG

8,7391 gold badge26 silver badges33 bronze badges

Comments

Gerrat · Accepted Answer · 2011-07-11 18:00:24Z

1

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

answered Jul 11, 2011 at 18:00

Gerrat

29.9k9 gold badges78 silver badges104 bronze badges

Comments

Brad Mace · Accepted Answer · 2011-07-11 23:41:42Z

1

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

edited Jul 11, 2011 at 23:41

Brad Mace

28k19 gold badges110 silver badges152 bronze badges

answered Jul 11, 2011 at 18:01

user839397

1291 silver badge3 bronze badges

Comments

Ken White · Accepted Answer · 2023-02-22 02:50:40Z

1

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

edited Feb 22, 2023 at 2:50

answered Jul 11, 2011 at 18:00

Ken White

126k15 gold badges237 silver badges476 bronze badges

Collectives™ on Stack Overflow

Help in writing an sql query

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related