generate frequency distribution from database

Question

In a MySQL database there's a table with a single numeric value column. I want to plot the distribution of these values as a bar chart/histogram with the following requirements:

• there should be a maximum of N bars (intervals) in the chart
• the width of each bar (x-axis range) should be uniform and the height of each bar should reflect the number of values in this interval.
• the endpoints of the bar should occur at round numbers. I understand that this is a fairly vague requirement, but hopefully the following example will illustrate what I mean
• the intervals should be contiguous, e.g. the next interval should start where the previous one ended
• ideally it should be possible to retrieve the data with a single query
• it's OK to have intervals with a count (y-axis value) of 0

Example

If N = 3 and the table contains the following data

+------------+
| value      |
+------------+
|     -49.2  |
|     -28.2  |
|      13.3  |
|      23.3  |
|      51.4  |
|      77.9  |
+------------+

On inspection it's fairly easy to see that the intervals {-50..0, 0..50, 50..100} satisfy the requirements for this dataset and value of N.

However, I'm struggling to come up with a general solution that works for any value of N and any dataset. Here's what I've tried so far:

Calculate Interval Width

Get the max and min value via the following query

SELECT min(value), max(value), count(*) FROM my_table

Then pass the result into this (Groovy/Java) method to calculate the width of each interval

// intervalCount is what I've referred to as "N"
static Integer getRoundedIntervalSize(Double min, Double max, Integer intervalCount) {
    Number intervalSize = Math.ceil((max - min) / intervalCount)

    Integer roundingScale = Math.log10(intervalSize) - 1
    Number roundNearest = 10 ** roundingScale

    // round up the interval size to the nearest roundNearest
    Number intervalDelta = roundNearest - (intervalSize % roundNearest)
    intervalSize + intervalDelta
}

Get Frequency Distribution

I then use the following query (with the value returned by getRoundedIntervalSize substituted for :groupSize) to get the number of values in each interval

SELECT      floor(value / :groupSize) * :groupSize  as groupLowerLimit,
            count(*) as groupCount
FROM        my_table
GROUP BY    groupLowerLimit
ORDER BY    groupLowerLimit ASC

This returns the lower limit of each interval and the number of values in each interval, which is all I need to build the frequency distribution.

Shortcomings

Although this approach works fairly well when the dataset is relatively uniformly distributed, when this is not the case, it results in intervals which have different widths or are not contiguous. Also, when the range of the dataset is small (e.g all values between 1 and 4) and N is large (e.g. 30), the number of intervals generated tends to be much smaller than N.

Is there a better approach to solving this problem that meets the requirements above?