Grouping by not working correctly

Question

I have a guests table, with fields name and country, both varchar.

At the moment, I have two records, one with country being USA, and one with country being USA.

I am trying to group my records and then order them to see the most populous.

I first tried this:

SELECT *
FROM (
SELECT count( country) AS countryCount, country
FROM guests
GROUP BY countryCount
)Q
ORDER BY Q.countryCount DESC
LIMIT 0 , 5

Which gives the error: Can't group on 'countryCount'

I then removed the group by line

And tried

SELECT *
FROM (
SELECT count( country) AS countryCount, country
FROM guests
)Q
ORDER BY Q.countryCount DESC
LIMIT 0 , 5

This returns only one row, with countryCount as 2, and for country returns denmark

What happened to the USA record?

I am doing a similar thing for a products table, and it works perfectly:

SELECT *
FROM (
SELECT count( product) AS productCount, product
FROM sales
GROUP BY product
)Q
ORDER BY Q.productCount DESC
LIMIT 0 , 5

Answer 1

select count(*) as c, country from guests group by country order by c desc

Answer 2

Try the following - you shouldn't need to nest your SELECT statements, and your GROUP BY seemed wrong too;

SELECT country, count( country) AS countryCount
FROM guests
GROUP BY country
ORDER BY countryCount DESC

Answer 3

Use:

  SELECT COUNT( country) AS countryCount, country
    FROM guests
GROUP BY country
ORDER BY countryCount DESC
LIMIT 0 , 5

You specify the column(s) that are not wrapped in aggregate functions (COUNT, MAX/MIN, etc) in the GROUP BY clause.

Don't need the subquery because you can reference column aliases in the ORDER BY clause.

Answer 4

You need to group by the unique field:

SELECT *
FROM (
SELECT count( country) AS countryCount, country
FROM guests
GROUP BY country
)Q
ORDER BY Q.countryCount DESC
LIMIT 0 , 5