CODEWITHSUNDEEP
sqlcount
Jonnie
Jonnie

Reputation: 131

SQL return multiple count statements

I have a table which contains ski resorts around the world. The table also identifies which country and "sector" (USA/Europe/Asia etc.) they are located in. I'm trying to determine the number of resorts in each sector.

Sample Data : 

resort_id, resort_name, country_id, sector_id
3376    Chréa           204  204
3377    Tikjda          204  204
3384    Beidahu         208  205
3481    Canyon Ski Area 225  206
3482    Castle Mountain 225  206
3483    Drumheller      225  206

I need to be able to determine the number of resorts in each sector i.e. :

Sector       Resorts
--------------------
204          2
205          1
206          3

Any help would be much appreciated thanks.

Upvotes: 2

Views: 70

Answers (3)

paul
paul

Reputation: 22001

select sector_id,
       count(*)
from   resortTableName
group by sector_id

and to address your edited question:

select sector_id,
       count(distinct resort_name) as resortCount,
       count(distinct country_id) as countryCount
from   resortTableName
group by sector_id

Upvotes: 6

Jens
Jens

Reputation: 69440

It is a simple group By

Select count(*) As Resorts, sector_id as Sector 
from yourtablename 
group by sector_id

Upvotes: 1

Mureinik
Mureinik

Reputation: 310983

You just need to count and group the results by the sector:

SELECT   sector_id, COUNT(*)
FROM     resorts
GROUP BY sector_id

Upvotes: 2

Related Questions