In Sql, How to "Group By" in 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

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.

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 Grouping by on a range of year

You can try this query. This will help you in the simplest way.

SELECT  COUNT(*),(CONVERT(VARCHAR,MIN(year(orderdate)))+'-' + CONVERT(VARCHAR,MAX(year(orderdate)))) AS yearRange
FROM orders
GROUP BY FLOOR(Year(OrderDate) /5)

You Can refer the link SQL_Fiddle_Link that uses your example to form this query.

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

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 group query results by user id and date ranges dynamically

With all weeks starting on Monday, this would do it (efficiently):

SELECT id AS user_id, u."onboardedAt", u."closedAt"
     , week_start, COALESCE(t.tx_count, 0) AS tx_count, a.last_user_action
FROM   "Users" u
CROSS  JOIN  generate_series(date_trunc('week', u."onboardedAt"), u."closedAt", interval '1 week') AS week_start
LEFT   JOIN (
   SELECT "userId" AS id, date_trunc('week', t."createdAt") AS week_start, count(*) AS tx_count
   FROM   "Transactions" t
   GROUP  BY 1, 2
   ) t USING (id, week_start)
LEFT   JOIN (
   SELECT DISTINCT ON (1, 2)
          "userId" AS id, date_trunc('week', a."createdAt") AS week_start, action AS last_user_action
   FROM   "UserActions" a
   ORDER  BY 1, 2, "createdAt" DESC
   ) a USING (id, week_start)
ORDER  BY id, week_start;

db<>fiddle here

Working with standard weeks makes everything much simpler. We can aggregate in the "many" tables before joining, which is simpler and cheaper. Else, multiple joins can go wrong quickly. See:

Two SQL LEFT JOINS produce incorrect result

Standard weeks make it easier to compare data, too. (Note that first and last week per user can be truncated (span fewer days). But that applies to the last week per user in any case.)

The LATERAL keyword is assumed automatically in a join to a set-returning function:

CROSS  JOIN  generate_series(...)

See:

What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

Using DISTINCT ON to get the last_user_action per user. See:

Select first row in each GROUP BY group?

I advise to user legal, lower-case identifiers, so double-quoting is not required. Makes your life with Postgres easier.

Use last non-null action

Added in a comment:

if action is null in a current week, I want to grab most recent from previous weeks

SELECT user_id, "onboardedAt", "closedAt", week_start, tx_count
     , last_user_action AS last_user_action_with_null
     , COALESCE(last_user_action, max(last_user_action) OVER (PARTITION BY user_id, null_grp)) AS last_user_action
FROM  (
   SELECT id AS user_id, u."onboardedAt", u."closedAt"
        , week_start, COALESCE(t.tx_count, 0) AS tx_count, a.last_user_action
        , count(a.last_user_action) OVER (PARTITION BY id ORDER BY week_start) AS null_grp
   FROM   "Users" u
   CROSS  JOIN  generate_series(date_trunc('week', u."onboardedAt"), u."closedAt", interval '1 week') AS week_start
   LEFT   JOIN (
      SELECT "userId" AS id, date_trunc('week', t."createdAt") AS week_start, count(*) AS tx_count
      FROM   "Transactions" t
      GROUP  BY 1, 2
      ) t  USING (id, week_start)
   LEFT   JOIN (
      SELECT DISTINCT ON (1, 2)
             "userId" AS id, date_trunc('week', a."createdAt") AS week_start, action AS last_user_action
      FROM   "UserActions" a
      ORDER  BY 1, 2, "createdAt" DESC
      ) a USING (id, week_start)
   ) sub
ORDER  BY user_id, week_start;

db<>fiddle here

Explanation:

Retrieve last known value for each column of a row

SQL Server: group dates by ranges

You need to do something like this

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'
    when score between 20 and 29 then '20-29'
    ...
    else '90-99' end as range
  from scores) t
group by t.range

Check this link In SQL, how can you "group by" in ranges?

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]

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