Histogram via SQL Query - How to include 0 rows?

[Armitage]

Say I have a table with a range column (float) where the values are from 1 to 10. I want to see the distribution of rows according to range. I can do something like this:

SELECT floor(range) AS range_bin, COUNT(*) FROM tbl_name GROUP BY range_bin ORDER BY range_bin;

and get an output like this:

range_bin count(*)
0 5
1 22
4 17
5 12
6 10
7 7
9 3

Note that there are no rows for range_bin = 2,3,8 because the data didn't have any values in that range.
Is there a way to get the query to include those rows with a 0 in the count(*) column??

For non-trivial, multidimensional histograms this is a PITA when you go to plot the data.

If it matters, this is on MySQL v4.1

 

[BoberFett]

There may be a better way to do it that I haven't figured out, but when I ran into something like this once the quick and dirty was to create a temporary table with the range values I wanted to evaluate and then left join the results to it. That'll force your output to include all of your ranges, even for 0 counts.

 

[Armitage]

Originally posted by: BoberFett
There may be a better way to do it that I haven't figured out, but when I ran into something like this once the quick and dirty was to create a temporary table with the range values I wanted to evaluate and then left join the results to it. That'll force your output to include all of your ranges, even for 0 counts.

Yea, I though if that also ... just not clear on how to apply it to the multidimensional case ... say I was trying to bin on range AND velocity.