How to Create an SQL Query That Groups by Value Ranges

In SQL, how can you group by in ranges?

Neither of the highest voted answers are correct on SQL Server 2000. Perhaps they were using a different version.

Here are the correct versions of both of them on SQL Server 2000.

select t.range as [score range], count(*) as [number of occurences]
from (
  select case  
    when score between 0 and 9 then ' 0- 9'
    when score between 10 and 19 then '10-19'
    else '20-99' end as range
  from scores) t
group by t.range

or

select t.range as [score range], count(*) as [number of occurrences]
from (
      select user_id,
         case when score >= 0 and score< 10 then '0-9'
         when score >= 10 and score< 20 then '10-19'
         else '20-99' end as range
     from scores) t
group by t.range

SQL: GROUP BY for number ranges?

This would be easiest with a case statement (assuming you're using sql-server; if not, please add tags for the correct version).

Select [Numbers]
  , case when [Numbers] between 1 and 999 then 'AA'
         when [Numbers] between 1000 and 6999 then 'BB'
         when [Numbers] between 7000 and 9999 then 'CC'
    end as [Numbers Level]
from MyTable

If any of your numbers don't fall into those ranges, it will return NULL - use else if you want a different result when this happens.

How do I create an SQL query that groups by value ranges

SELECT B.Description, Total = COUNT(*) / CONVERT(money, (SELECT COUNT(*) FROM Target T2))
FROM Target T
JOIN (
    SELECT  Description = '0 to 10', LBound = 0, UBound = 10 
    UNION ALL 
    SELECT Description = '10 to 20', LBound = 10, UBound = 20
) B ON T.Value >= LBound AND T.Value < B.UBound
GROUP BY B.Description

Grouping by a range of values in SQL

select sum(case when value < 500 then 1 else 0 end) as [less than 500],
       sum(case when value >= 500 and value <= 900 then 1 else 0 end) as [500 to 900],
       sum(case when value > 900 then 1 else 0 end) as [above 900]
    from YourTable

EDIT: To address Dalen's concern from the comments below and provide output in the exact format given in the question:

select 'less than 500' as Caption, count(*) as Count
    from YourTable
    where value < 500
union all
select '500 to 900' as Caption, count(*) as Count
    from YourTable
    where value >= 500 and value <= 900
union all
select 'above 900' as Caption, count(*) as Count
    from YourTable
    where value > 900

And, for SQL Server 2005+, you could improve on this by leveraging UNPIVOT with my original query:

select Caption, Count
    from (select sum(case when value < 500 then 1 else 0 end) as [less than 500],
                 sum(case when value >= 500 and value <= 900 then 1 else 0 end) as [500 to 900],
                 sum(case when value > 900 then 1 else 0 end) as [above 900]
              from YourTable) t
unpivot (Count for Caption in ([less than 500], [500 to 900], [above 900])) p

SQL - Group values by range

You can use generate_series() and a range type to generate the the ranges you want, e.g.:

select int4range(x.start, case when x.start = 1000 then null else x.start + 100 end, '[)') as range
from generate_series(0,1000,100) as x(start)

This generates the ranges [0,100), [100,200) and so on up until [1000,).

You can adjust the width and the number of ranges by using different parameters for generate_series() and adjusting the expression that evaluates the last range

This can be used in an outer join to aggregate the values per range:

with ranges as (
  select int4range(x.start, case when x.start = 1000 then null else x.start + 100 end, '[)') as range
  from generate_series(0,1000,100) as x(start)
)  
select r.range as metric,
       sum(t.value)
from ranges r
  left join the_table t on r.range @> t.metric
group by range;

The expression r.range @> t.metric tests if the metric value falls into the (generated) range

Online example

is there a way to group by using values in a range in sql

If you just want the counts, then you don't even need to group, you can just use conditional aggregation:

with  -- For testing only; remove and use actual table name in SELECT statement
  your_table (ref_num, tran_amt) as (
    select 1612,     2500   from dual union all
    select 1613, 51800000   from dual union all
    select 1614,  2170000   from dual union all
    select 1615,      100   from dual union all
    select 1616,  2442876.5 from dual union all
    select 1617,    25000   from dual union all
    select 1618,      250   from dual union all
    select 1619,     7000   from dual union all
    select 1610,    51500   from dual union all
    select 1621,    15000   from dual union all
    select 1622,       20   from dual
  )
select count (case when tran_amt <=  5000 then 1 end) as amt_to_5000,
       count (case when tran_amt >   5000 
                    and tran_amt <= 50000 then 1 end) as amt_from_5000_to_50000,
       count (case when tran_amt >  50000 then 1 end) as amt_over_50000
from   your_table
;

AMT_TO_5000 AMT_FROM_5000_TO_50000 AMT_OVER_50000
----------- ---------------------- --------------
          4                      3              4

Note how I use non-strict and strict inequalities, not between. With between, you would miss amounts like 5000.83 - they would not be counted anywhere.

SQL Group By custom range?

One possible way is using the same CASE WHEN statement for the GROUP BY clause, for example* :

SELECT CASE 
    WHEN Quantity <= 6 THEN CAST(Quantity as VARCHAR(1))
    ELSE '>6' 
    END AS Range, COUNT(Quantity) AS Amount
FROM  orders
GROUP BY (CASE 
    WHEN Quantity <= 6 THEN CAST(Quantity as VARCHAR(1))
    ELSE '>6' 
    END)        

*) Using simplified version of your original CASE WHEN statement. Written in SQL Server flavor of SQL.

SQL query grouping by range

I understand this as a gaps-and-island problem, where you want to group together adjacent rows that have the same PA.

Here is an approach using the difference between row numbers to build the groups:

select min(year_month) year_month_start, max(year_month) year_month_end, sum(amount) amount
from (
    select a.*, year * 100 + month year_month
        row_number() over(order by year, month) rn1,
        row_number() over(partition by pa order by year, month) rn2
    from a
) a
group by rn1 - rn2
order by year_month_start

How to group count values into ranges like exactly one, 2 to 5, more than 5

Create a (virtual) table that stores the ranges, then join it with the aggregate data:

SELECT ranges.*, COUNT(*) AS ReceiversInRange --, other aggregates
FROM (VALUES
    (1,  1,  1, 'Low risk'),
    (2,  2,  5, 'Low risk'),
    (3,  6, 10, 'Medium risk'),
    (4, 11, 20, 'Medium-high'),
    (5, 20, NULL, 'High risk')
) AS ranges(id, lb, ub, label)
INNER JOIN (
    SELECT COUNT(DISTINCT Sender) AS SenderCount --, other aggregates
    FROM t
    GROUP BY Receiver 
) AS aggdata ON SenderCount >= lb AND (SenderCount <= ub OR ub IS NULL)
GROUP BY id, lb, ub, label

Grouping by a SUM range of values SQL

You can put your select statement into a subquery. I assumed that Serialnumber is a UserID and that you want to count distinct users in each group.

     Select [Sum of PAYMENTAMOUNT] as 'Range', 
count(distinct Serialnumber) as  'Count of UserIDs' FROM
            (
            SELECT  
             SERIALNUMBER ,  
        Case when SUM(BATCHDETAIL.PAYMENTAMOUNT) > 1 and SUM(BATCHDETAIL.PAYMENTAMOUNT) < 500 then 'small' 
        when SUM(BATCHDETAIL.PAYMENTAMOUNT) > 499 and SUM(BATCHDETAIL.PAYMENTAMOUNT) < 5000 then 'medium' 
        when SUM(BATCHDETAIL.PAYMENTAMOUNT) > 5000 and SUM(BATCHDETAIL.PAYMENTAMOUNT) < 10000000 then 'large' END  AS 'Sum of PAYMENTAMOUNT'  
            FROM BATCHDETAIL
            WHERE  (DATEOFPAYMENT > '2017/07/31') AND (DATEOFPAYMENT < '2018/08/01')  
        GROUP BY SERIALNUMBER
        ) s
 group by [Sum of PAYMENTAMOUNT]

---

Related Topics