Replace mysql->php->mysql with nested query

Question

Hello all, I have a table with columns 'customer' and 'location' and I need to check top 10 locations visited by unique customers.

I'm ready to do the following:

1. SELECT location FROM myTable GROUP BY location
2. load results to an array (maybe a hundred of few hundreds of locations)
3. SELECT COUNT(*) AS total, tbl.location AS LOCATION FROM (SELECT DISTINCT customer FROM myTable WHERE location = location_inserted_by_php) as tbl;

Now, this sounds like a rather stupid solution, but I'm rather unacquainted with nested queries. Any help?

Thanks

Answer 1

SELECT location,count(distinct customer) as visitors from customer_locations
GROUP BY location
ORDER BY visitors DESC
LIMIT 10

Gets the top 10 locations by number of unique visitors