How can I calculate rankings of unique values in MySQL?

Question

Lets say I have a simple table with 'appID' and 'userID', which his populated for each app that used by a specific user (ever). (These IDs refer to other tables which have information about app and user which do not matter for this question).

For example:

appID  userID
1      1
1      2
2      2

Here, we can see app #1 was used by two users, but app #2 was used by one user.

I want to generate statistics for the most commonly used app using a MySQL query, if possible. The query would return a list of sorted results with the appID and the total number of unique users using it.

I did some research but cannot figure out an easy to do this in SQL. If anyone can help, I'd appreciate it. If it requires a very long and involved stored procedure, I may just switch to doing some of the calculations in code (at least initially) since it will be easier to troubleshoot.

Answer 1

SELECT appID, COUNT(*) FROM myTable GROUP BY appID ORDER BY COUNT(*) DESC