CODEWITHSUNDEEP
mysql
Jan
Jan

Reputation: 248

Count total results found based on country

I have a query that displays columns from multiple tables in a test dbase. The output are hotels from all over the world with their corresponding information. My search criteria is simple; no blank fields and I want their phone numbers that don't start with a + sign.

It works but it displays all entries (like it should) BUT is it possible to only display the total number of items found, for example, per country? For example it displays 5000 lines in China, 3458 lines in Italy, 2050 lines in Canada etc etc.. Is it possible to just display the total amounts found? 5000 : China 3458 : Italy 2050 : Canada etc etc

I've tried COUNT and HAVING but it hasn't worked for me so far. I also looked up a few topic here but still no success. This is my query:

SELECT 
hotel.id HotelID, 
hotel.name Hotel_Name, 
hotel.tel_area Area_Number,
hotel.tel Phone_Number,
city.name City,
region.name Region,
country.name Country,
country.id CountryID,
country.continent Continent,
hotel.web Website

FROM mydata.hotel      as hotel
JOIN mydata.city       as city     ON hotel.city = city.id
JOIN mydata.region     as region   ON region.id = city.region
JOIN mydata.country    as country  ON country.id = region.country

where hotel.tel not like ''
and accos.tel not like '+%'

order by country.name

Anybody suggestions?

Upvotes: 2

Views: 496

Answers (2)

PLB
PLB

Reputation: 891

My SQL is a bit rusty but... what about GROUP BY?

The syntax I found is:

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

Alternatively, at the end of this page they give an explanation in layman terms.

I hope this helps!

Upvotes: 0

fancyPants
fancyPants

Reputation: 51888

SELECT 
country.name Country,
COUNT(*)
FROM mydata.hotel      as hotel
JOIN mydata.city       as city     ON hotel.city = city.id
JOIN mydata.region     as region   ON region.id = city.region
JOIN mydata.country    as country  ON country.id = region.country

where hotel.tel not like ''
and accos.tel not like '+%'
GROUP BY Country
/*an ORDER BY is not really needed in this case, in MySQL GROUP BY contains an implicit ORDER BY*/

Upvotes: 2

Related Questions