PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How do I get an aggregate count for data within a range?

Perhaps, I’m overthinking this, but I can’t seem to figure out how to take a list of decimals and group it into data ranges and get a count for each range. Ideally, I would want the minimum value to always be zero regardless of the selected data, and the max should be the max value of the data set. Then I should be able to provide a divisor which would define how many groups I want the data to be divided into. I would prefer not to use a temp table is possible

sample set:

150.75
250.01
600.10
900.50

The output with a divisor of 2 would be:

| RangeStart | RangeEnd | Count |
| ---------- | -------- | ----- |
| 0          | 450.25   |   2   |
| 450.26     | 1000     |   2   |
Tags:

2

Answers


  1. 0

    Here is the query:

    WITH ranges AS (
    WITH datas AS (
        SELECT
            10 AS group_count,
            MAX(table_name.column_name) AS maximum
        FROM
            table_name
    )
    SELECT
        group_no,
        datas.maximum / datas.group_count * group_no AS group_min,
        datas.maximum / datas.group_count * (group_no + 1) AS group_max
    FROM
        datas,
        generate_series(0, datas.group_count - 1) AS group_no
)
SELECT
    ranges.group_no,
    ranges.group_min,
    ranges.group_max,
    COUNT(table_name)
FROM
    ranges
    LEFT JOIN table_name
    ON (table_name.column_name >= ranges.group_min AND table_name.column_name < ranges.group_max)
GROUP BY
    ranges.group_no,
    ranges.group_min,
    ranges.group_max
ORDER BY
    ranges.group_no

    You need to replace table_name and column_name with proper names.

    Short explanation:

    I use (nested) WITH queries. First named datas is used to pass the parameter how many groups you want to divide to (10 in this example), and to count the maximum value from the dataset.

    The second WITH query (ranges) is used to get minimum and maximum value of each group. I use group_no for easy grouping.

    And the main query JOINS the data table with groups definitions (notice the join condition), and counts rows in each group.

    Have fun 😉

    Login or Signup to reply.
  2. 0

    Define value ranges for group as (group_number-1)*step->(group_number)*step.
    Group_numbers from 1 to groupCount.
    Step is max(value)/groupCount.
    Join ranges with source table and group by group_number.

    See example

    select grn,min(startRange)startRange,max(endRange)endRange,count(val) qty
from(
  select *
    ,maxV/groupCount*(grn-1) startRange
    ,case when grn<groupCount then maxv/groupCount*grn else maxv+0.1 end endRange
  from (
      select (select max(val) from test) as maxv
          ,2 as groupCount
        ) as param
      cross join generate_series(1,groupCount) grn
)rngs
left join test t on t.val>=startRange and t.val<endRange
group by grn
order by grn

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search