MySQL and CASE WHEN with a range of values

Question

I have an accounts table and a records table where accounts have multiple records. I would like to break down the account totals by "count of records" range. I.e. Show the breakdown of

Count of Records | Count ========================= 0-25 | 100 25 - 50 | 122 50 - 100 | 300 

Etc.

I am using the following query, but I can't get it to group by "grp" which is what I want, any help on the best way to modify query?

SELECT count(*) as ct,     CASE          WHEN COUNT(*) < 25 THEN '1-25'         WHEN COUNT(*) >= 25 < 50 THEN '25-50'         WHEN COUNT(*) >= 50 < 100 THEN '50-100'         WHEN COUNT(*) >= 100 < 250 THEN '100-250'         WHEN COUNT(*) >= 250 < 500 THEN '250-500'         WHEN COUNT(*) >= 500 < 1000 THEN '500-1000'         ELSE '1000+'     END AS grp     FROM records r,accounts a     WHERE r.account_id=a.id     ORDER BY ct 

Answer 1

try this:

SELECT count(*) as ct,  CASE       WHEN COUNT(*) < 25 THEN '1-25'      WHEN COUNT(*) >= 25 < 50 THEN '25-50'      WHEN COUNT(*) >= 50 < 100 THEN '50-100'      WHEN COUNT(*) >= 100 < 250 THEN '100-250'      WHEN COUNT(*) >= 250 < 500 THEN '250-500'      WHEN COUNT(*) >= 500 < 1000 THEN '500-1000'      ELSE '1000+'  END AS grp  FROM records r, accounts a  WHERE r.account_id=a.id  GROUP BY r.account_id, a.id,      CASE       WHEN COUNT(*) < 25 THEN '1-25'      WHEN COUNT(*) >= 25 < 50 THEN '25-50'      WHEN COUNT(*) >= 50 < 100 THEN '50-100'      WHEN COUNT(*) >= 100 < 250 THEN '100-250'      WHEN COUNT(*) >= 250 < 500 THEN '250-500'      WHEN COUNT(*) >= 500 < 1000 THEN '500-1000'      ELSE '1000+' END ORDER BY count(*) 

You have to "define" the "buckets" you wish to aggregate the original data rows into... This is what the Group By clause is for... It defines the criteria by which each row in the base tables will be analyzed to determine which "bucket" it's data will be aggregated into... The expression or expressions defined in the group by clause are the "definitions" for those buckets.

As the query processes the original data rows, any row for which the value(s) of this expression(s) are the same as an existing bucket is aggregated into that bucket... Any new row with a value not represented by an existing bucket causes a new bucket to be created...