How to do a MySQL GROUP BY with ORDER BY on a COUNT()

MySQL Group by returns the data of the same value into a set rows. It is often use with COUNT, SUM or AVG etc. Doing this will return how often that GROUP BY value is found.

Order by simply orders the data either by ASC (ascending) or DESC (descending).

Combing these two into a query has its benefits. Assuming you wanted to see how many times a username has logged in from most to least, you need a COUNT, GROUP BY and ORDER BY.

Just throwing these into a query unfortunately doesn’t work, they need to be sequential.

Based on the above scenario here is a working MySQL query that has a COUNT, GROUP BY and ORDER BY

SELECT username, COUNT(*) AS the_count
FROM login_data
GROUP BY username
ORDER BY the_count DESC

It will return every username in the login_data table with how many times they have logged in (based on how many times their username appears) ordered by most to least (DESC).