Search code examples
mysqlcountdistributionfrequency

Mysql create freqency distribution

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:

  1. Creates an output similar to above, always creating 10 ranges of counts.
  2. (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?

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.