Create 'buckets' to count fields longer than certain length

[elsenorjose]

I have a table with, among other fields, a field with media file names. I want to create a query that would allow me to create 'buckets' which count how many records exceed a certain length. This is what my output would theoretically look like:

length     count
50         100
100        50
1000       30

Using MySQL 5.0.27 on Windows XP SP2

Thank you.

 

[towerbase]

I'm sure Rudy will come up with an elegant solution but how about something like

SELECT 50 AS length
     , SUM( CASE WHEN LENGTH(mediafn)>50 THEN 1 ELSE 0 END ) AS count
  FROM table

UNION

SELECT 100 AS length
     , SUM( CASE WHEN LENGTH(mediafn)>100 THEN 1 ELSE 0 END ) AS count
  FROM table

UNION

SELECT 1000 AS length
     , SUM( CASE WHEN LENGTH(mediafn)>1000 THEN 1 ELSE 0 END ) AS count
FROM table

Andrew
Hampshire, UK

 

[r937]

that looks elegant enough


[small]except i'd use UNION ALL instead[/small]





r937.com | rudy.ca

 

[elsenorjose]

Any performance issues I should be concerned about on a table with ~65 million rows? It's on a machine with a dual core 3Ghz CPU with 3GB of RAM. MySQL is pretty much all that's running on there but I'm still wondering if the query would run really slowly.

Thank you both for your help.