CODEWITHSUNDEEP
mysqlsql
Jkt44
Jkt44

Reputation: 47

Limiting table to amount of records that have specific value in one of columns

My task is to select countries whose amount of cities is higher than the amount of cities that have Canada as a country in the table. I'm using city and country tables from sakila database. Now, I've got something like this:

SELECT country, COUNT(country) FROM ( city
  INNER JOIN country ON country.country_id = city.country_id)
  GROUP BY country
  HAVING COUNT(country) >= 7 -- should be COUNT(country='Canada') or something like that
  ORDER BY COUNT(country) DESC;

Number 7 in 4th line is amount of cities, where country column equals 'Canada' but I have no idea how to count that using SQL nor could I find a correct way using google. Any suggestions?

Upvotes: 1

Views: 67

Answers (1)

h33
h33

Reputation: 1344

You'll need to put a subquery into your HAVING clause.

The following query gets the number of cities in canada.

SELECT count(*) 
FROM city
LEFT JOIN country ON city.country_id = country.country_id
WHERE country = 'Canada'
GROUP BY country

So you put this into your having clause.

SELECT 
    country, COUNT(*) 
FROM 
    city
INNER JOIN 
    country ON country.country_id = city.country_id
GROUP BY 
    country
HAVING 
    COUNT(country) >= (
        SELECT count(*) 
        FROM city
        LEFT JOIN country ON city.country_id = country.country_id
        WHERE country = 'Canada'
        GROUP BY country
    )
ORDER BY 
    COUNT(country) DESC;

Upvotes: 1

Related Questions