Mysql create freqency distribution

count, distribution, frequency, mysql

Solution

SELECT
    FLOOR( birds.bird_count / stat.diff ) * stat.diff as range_start, 
    (FLOOR( birds.bird_count / stat.diff ) +1) * stat.diff -1 as range_end, 
    count( birds.bird_count ) as times_seen
FROM birds_table birds, 
    (SELECT 
        ROUND((MAX( bird_count ) - MIN( bird_count ))/10) AS diff
    FROM birds_table
    ) AS stat
GROUP BY FLOOR( birds.bird_count / stat.diff )

Here You have answer for both of Your questions ;] with difference that start and end of range are in separate columns instead of concatenated but if You need it in one column I guess You can do it from here. To change number of ranges just edit number 10 You can find in sub-query.

Problem

I have a simple table BIRDCOUNT below, showing how many birds were counted on any given day: ``` +----------+ | NUMBIRDS | +----------+ | 123 | | 573 | | 3 | | 234 | +----------+ ``` I would like to create a frequency distribution graph, showing how many times a number of birds were counted. So I need MySQL to create something like: ``` +------------+-------------+ | BIRD_COUNT | TIMES_SEEN | +------------+-------------+ | 0-99 | 17 | | 100-299 | 23 | | 200-399 | 12 | | 300-499 | 122 | | 400-599 | 3 | +------------+-------------+ ``` If the bird count ranges were fixed this would be easy. However, I never know the min/max of how many birds were seen. So I need a select statement that: - Creates an output similar to above, always creating 10 ranges of counts. - (more advanced) Creates output similar to above, always creating N ranges of counts. I don't know if #2 is possible in a single select but can anyone solve #1?

Original source