Is SELECT COUNT GROUP BY more efficient than counting a result set?

I have a table with a rank column which can be either 1, 2 or 3. I need to count the number of records for each rank, but I also need to display all the data ordered by rank.

From my perspective, the two ways to accomplish this would be:

-
Pull out the data with a normal SELECT and then run a second query to obtain counts:

SELECT rank, COUNT(id) FROM tablename GROUP BY rank

• Pull out the data with a normal SELECT and then iterate twice over the result set I get: once to count the occurrences of each rank, and the second time to actually display the data.

Which way would be more performant? Does it depend on table size? I imagine for large tables the SELECT COUNT would be a tad slower than counting in (PHP, ASP.NET, Java), in particular if there's more than 3 discrete values I want to count.

The recommended approach to improve performance is to:

a) Use the database to do the heavy lifting for counting, computations (where possible)

b) Reduce the number of trips to the database and the amount of data brought back on each trip

In that light, I am not sure what you want to use the rank count for, but the query below will return the data ordered by rank, but a "repeated" column with the number of records for each rank is also included

SELECT t.rank, col1, col2, rankcount 
FROM tablename t 
  INNER JOIN 
     (SELECT rank, COUNT(id) as rankcount FROM tablename t2 GROUP BY rank) AS t3 
    ON t.rank = t3.rank  
ORDER BY t.rank

StackExchange Database Administrators Q#16656, answer score: 3