I have to make a query that has the Total number of customers by country and city
country and city are columns that are inside the customer table
On my own I have managed to get the total number of customers per city like this:
SELECT city, COUNT (*)
FROM employees
GROUP BY city
ORDER BY city
But how do I get it together with the country? looking for information I think it should be something like this and ordered from largest to smallest
| Country | City | TOTAL_CUSTOMERS |
|---|---|---|
| USA | Kirkland | 3 |
| USA | London | 2 |
| UK | Redmond | 2 |
| UK | Seattle | 1 |
| UK | Tacoma | 1 |
What we have been told is to say Total number of customers by country and city.
CodePudding user response：
You simply add country to the column list and group by list:
SELECT country,city, COUNT(*)
FROM employees
GROUP BY country,city
ORDER BY COUNT(*) DESC