I was asked this question earlier, and not being able to answer it has been bothering me..
Lets say we have a table with clients and their city of residence.
What if I wanted to query for the unique number of cities, AS WELL as how many times each city appears.
To be more clear:
Toronto
Toronto
Toronto
Oshawa
Oshawa
Distinct cities: 2
Toronto: 3
Oshawa: 2
The answer is probably simple, my mind is just mush at the moment!
This will return a row for each city, containing the city name and the number of times the city appears:
SELECT
city,
COUNT(*)
FROM clients
GROUP BY city
The total number of cities is just the number of rows returned by the query. If you really want it to appear on the recordset, I can think of two ways (none of which I particularly like).
UNION query:
SELECT 'CITIES' as city, COUNT(DISTINCT city)
FROM clients
UNION
SELECT city, COUNT(*)
FROM clients
GROUP BY city
Subquery on the SELECT clause:
SELECT
(SELECT COUNT(DISTINCT city)) AS all,
city,
COUNT(*)
FROM clients
GROUP BY city
Use a group by for this.
select city, count(*) from clients group by city
Here's the sql for selecting the distinct cities:
select distinct city from Clients;
Here's the sql for selecting the names along with how many there are:
select city, count(*) from Clients group by city;
