MySQL query - grouping count query

Question

My SQL to list the total number of products by location and show only if there are more than 2 units per product.

SELECT COUNT(*) AS 'count_product'
    FROM inventory
    GROUP BY product_no
    HAVING COUNT(DISTINCT location) >= 3
    ORDER BY product_no DESC;

This code gets me this output but I also want to add another column (location) from the base table.

+---------------+
| count_product |
+---------------+
|5              |
|4              |
|3              |
|2              |
+---------------+

I want the output to look like this:

+---------------+----------+
| count_product | location |
+---------------+----------+
|5              | Miami    |
|4              | Boston   |
|3              | Denver   |
|2              | Houston  |
+---------------+----------+

Answer 1

This depends on where the data exists. Does the city data exist on the Inventory table from which you are pulling or will you require a join? If it is on the table you already are pulling from it could look like this

SELECT COUNT(*) AS 'count_product', dbo.City As 'Location'
    FROM inventory
    GROUP BY product_no, dbo.location
    HAVING COUNT(DISTINCT location) >= 3
    ORDER BY product_no DESC, dbo.location;

If you need to do a join, a standard join on a small query like that should work, no need for anything fancy I wouldn't think. Also you could simply say having count(distinct location) > 2 since 3 is greater than two and so is everything else and there's no chance of it missing a 3?