How does one query for count() on a series of ranges at once?

Question

I'm trying to compose a breakdown of forum ranks on a phpBB forum. Forum ranks are defined by your postcount: if you have between 1-9 posts you're level 1; 10-24 and you're level 2; and so on. (The maximum rank is anything above 2000 posts.)

Right now, my forum rank statistics page is just doing a bunch of dumb queries:

SELECT COUNT(*) from _users WHERE post_count>=1 AND post_count<10;
SELECT COUNT(*) from _users WHERE post_count>=10 AND post_count<25;

... And so on.

The queries run in a few seconds, so I'm really just curious (for future reference) a better way to solve this problem - it feels like there ought to be one query I can run that'll return results akin to this:

rank_id | count
1       | 5000
2       | 2000
...

I do have another table which has a listing of the ranks and the minimum number of posts needed to achieve that rank. Not sure if that's necessary or if there's a more clever way to write my queries.

I'm using a MySQL DB if that helps any, but I'm much more interested in a cross-database answer.

Answer 1

I think you're looking for SELECT CASE...WHEN.

Answer 2

Stick a UNION between them.

SELECT COUNT(*) from _users WHERE post_count>=1 AND post_count<10;
UNION
SELECT COUNT(*) from _users WHERE post_count>=10 AND post_count<25;

Alternatively, you could use a CASE WHEN TO stack them and sum the results to get it on one row.

SELECT
   SUM(CASE WHEN post_count>=1 AND post_count<10 THEN 1 ELSE 0 END) AS Between_1_10
   SUM(CASE WHEN post_count>=10 AND post_count<25 THEN 1 ELSE 0 END) AS Between_10_25
   SUM(CASE WHEN post_count>=25 AND post_count<100 THEN 1 ELSE 0 END) AS Between_25_100
FROM 
   _users

Or another way to do it...

SELECT 
     Count(*) 
     , Category
FROM
(
    SELECT
       CASE 
          WHEN post_count>=1 AND post_count<10 THEN '1_To_10'
          WHEN post_count>=10 AND post_count<25 THEN '10_To_25'
          WHEN post_count>=25 AND post_count<100 THEN '25_To_100'
          ELSE 'Over_100' 
       END As Category
    FROM
       _users
) as InnerTable
GROUP BY
   Category