In SQL how can you group by in ranges

Question

Suppose I have a table with a numeric column  lets call it  score     I d like to generate a table of counts  that shows how many times scores appeared in each range   For example    score range    number of occurrences -------------------------------------    0-9                11   10-19               14   20-29                3                            In this example there were 11 rows with scores in the range of 0 to 9  14 rows with scores in the range of 10 to 19  and 3 rows with scores in the range 20-29   Is there an easy way to set this up   What do you recommend

User · Accepted Answer

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

User · Answer

select t range as score  count    as Count  from         select UserId           case when isnull score  0   gt   0 and isnull score  0  lt  5 then  0-5                  when isnull score  0   gt   5 and isnull score  0  lt  10 then  5-10                  when isnull score  0   gt   10 and isnull score  0  lt  15 then  10-15                  when isnull score  0   gt   15 and isnull score  0  lt  20 then  15-20                          else   20   end as range           case when isnull score  0   gt   0 and isnull score  0  lt  5 then 1                 when isnull score  0   gt   5 and isnull score  0  lt  10 then 2                 when isnull score  0   gt   10 and isnull score  0  lt  15 then 3                 when isnull score  0   gt   15 and isnull score  0  lt  20 then 4                       else 5  end as pd      from score table        t  group by t range pd order by pd

User · Answer

In postgres  where    is the string concatenation operator    select  score 10  10     -      score 10  10 9 as scorerange  count    from scores group by score 10 order by 1   gives    scorerange   count  ------------ -------  0-9             11  10-19           14  20-29            3  30-39            2

User · Answer

James Curran s answer was the most concise in my opinion  but the output wasn t correct  For SQL Server the simplest statement is as follows   SELECT       score range    CAST  Score 10  10 AS VARCHAR      -     CAST  Score 10  10 9 AS VARCHAR         number of occurrences    COUNT    FROM  Scores GROUP BY Score 10 ORDER BY Score 10   This assumes a  Scores temporary table I used to test it  I just populated 100 rows with random number between 0 and 99

User · Answer

This will allow you to not have to specify ranges  and should be SQL server agnostic  Math FTW    SELECT CONCAT range  -  range 9   COUNT range  FROM     SELECT      score -  score   10  as range   FROM scores

User · Answer

James Curran s answer was the most concise in my opinion  but the output wasn t correct  For SQL Server the simplest statement is as follows   SELECT       score range    CAST  Score 10  10 AS VARCHAR      -     CAST  Score 10  10 9 AS VARCHAR         number of occurrences    COUNT    FROM  Scores GROUP BY Score 10 ORDER BY Score 10   This assumes a  Scores temporary table I used to test it  I just populated 100 rows with random number between 0 and 99

User · Answer

select cast score 10 as varchar     -    cast score 10 9 as varchar           count    from scores group by score 10

User · Answer

James Curran s answer was the most concise in my opinion  but the output wasn t correct  For SQL Server the simplest statement is as follows   SELECT       score range    CAST  Score 10  10 AS VARCHAR      -     CAST  Score 10  10 9 AS VARCHAR         number of occurrences    COUNT    FROM  Scores GROUP BY Score 10 ORDER BY Score 10   This assumes a  Scores temporary table I used to test it  I just populated 100 rows with random number between 0 and 99

User · Answer

I see answers here that won t work in SQL Server s syntax   I would use   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   EDIT  see comments

User · Answer

Try  SELECT  str range     -    str range   9    AS  Score range   COUNT score  AS  number of occurances  FROM  SELECT  score   int score   10     10  AS range  FROM scoredata     GROUP BY range

User · Answer

select cast score 10 as varchar     -    cast score 10 9 as varchar           count    from scores group by score 10

User · Answer

In postgres  where    is the string concatenation operator    select  score 10  10     -      score 10  10 9 as scorerange  count    from scores group by score 10 order by 1   gives    scorerange   count  ------------ -------  0-9             11  10-19           14  20-29            3  30-39            2

User · Answer

This will allow you to not have to specify ranges  and should be SQL server agnostic  Math FTW    SELECT CONCAT range  -  range 9   COUNT range  FROM     SELECT      score -  score   10  as range   FROM scores

User · Answer

James Curran s answer was the most concise in my opinion  but the output wasn t correct  For SQL Server the simplest statement is as follows   SELECT       score range    CAST  Score 10  10 AS VARCHAR      -     CAST  Score 10  10 9 AS VARCHAR         number of occurrences    COUNT    FROM  Scores GROUP BY Score 10 ORDER BY Score 10   This assumes a  Scores temporary table I used to test it  I just populated 100 rows with random number between 0 and 99

User · Answer

An alternative approach would involve storing the ranges in a table  instead of embedding them in the query   You would end up with a table  call it Ranges  that looks like this   LowerLimit   UpperLimit   Range  0              9           0-9  10            19           10-19  20            29           20-29  30            39           30-39    And a query that looks like this   Select    Range as  Score Range      Count    as  Number of Occurences  from    Ranges r inner join Scores s on s Score between r LowerLimit and r UpperLimit group by Range   This does mean setting up a table  but it would be easy to maintain when the desired ranges change   No code changes necessary

User · Answer

create table scores      user id int     score int    select t range as  score range   count    as  number of occurences  from         select user id           case when score  gt   0 and score  lt  10 then  0-9           case when score  gt   10 and score  lt  20 then  10-19                        else  90-99  as range      from scores  t group by t range

User · Answer

create table scores      user id int     score int    select t range as  score range   count    as  number of occurences  from         select user id           case when score  gt   0 and score  lt  10 then  0-9           case when score  gt   10 and score  lt  20 then  10-19                        else  90-99  as range      from scores  t group by t range

User · Answer

Try  SELECT  str range     -    str range   9    AS  Score range   COUNT score  AS  number of occurances  FROM  SELECT  score   int score   10     10  AS range  FROM scoredata     GROUP BY range

User · Answer

select t blah 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 blah   from scores  t group by t blah   Make sure you use a word other than  range  if you are in MySQL  or you will get an error for running the above example

User · Answer

In postgres  where    is the string concatenation operator    select  score 10  10     -      score 10  10 9 as scorerange  count    from scores group by score 10 order by 1   gives    scorerange   count  ------------ -------  0-9             11  10-19           14  20-29            3  30-39            2

User · Answer

Perhaps you re asking about keeping such things going     Of course you ll invoke a full table scan for the queries and if the table containing the scores that need to be tallied  aggregations  is large you might want a better performing solution  you can create a secondary table and use rules  such as on insert - you might look into it   Not all RDBMS engines have rules  though

User · Answer

Because the column being sorted on  Range  is a string  string word sorting is used instead of numeric sorting   As long as the strings have zeros to pad out the number lengths the sorting should still be semantically correct   SELECT t range AS ScoreRange         COUNT    AS NumberOfOccurrences   FROM  SELECT CASE                     WHEN score BETWEEN 0 AND 9 THEN  00-09                      WHEN score BETWEEN 10 AND 19 THEN  10-19                      ELSE  20-99                 END AS Range           FROM Scores  t  GROUP BY t Range   If the range is mixed  simply pad an extra zero   SELECT t range AS ScoreRange         COUNT    AS NumberOfOccurrences   FROM  SELECT CASE                     WHEN score BETWEEN 0 AND 9 THEN  000-009                      WHEN score BETWEEN 10 AND 19 THEN  010-019                      WHEN score BETWEEN 20 AND 99 THEN  020-099                      ELSE  100-999                 END AS Range           FROM Scores  t  GROUP BY t Range

User · Answer

I see answers here that won t work in SQL Server s syntax   I would use   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   EDIT  see comments

User · Answer

create table scores      user id int     score int    select t range as  score range   count    as  number of occurences  from         select user id           case when score  gt   0 and score  lt  10 then  0-9           case when score  gt   10 and score  lt  20 then  10-19                        else  90-99  as range      from scores  t group by t range

User · Answer

I m here because i have similar question but i find the short answers wrong and the one with the continuous  quot case when quot  is to much work and seeing anything repetitive in my code hurts my eyes  So here is the solution SELECT --MIN score   MAX score        score range    CAST ROUND score-5 -1 AS VARCHAR      -     CAST  ROUND score-5 -1  10 AS VARCHAR        number of occurrences    COUNT    FROM order GROUP BY  CAST ROUND score-5 -1 AS VARCHAR      -     CAST  ROUND score-5 -1  10 AS VARCHAR  ORDER BY MIN score

User · Answer

An alternative approach would involve storing the ranges in a table  instead of embedding them in the query   You would end up with a table  call it Ranges  that looks like this   LowerLimit   UpperLimit   Range  0              9           0-9  10            19           10-19  20            29           20-29  30            39           30-39    And a query that looks like this   Select    Range as  Score Range      Count    as  Number of Occurences  from    Ranges r inner join Scores s on s Score between r LowerLimit and r UpperLimit group by Range   This does mean setting up a table  but it would be easy to maintain when the desired ranges change   No code changes necessary

User · Answer

I see answers here that won t work in SQL Server s syntax   I would use   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   EDIT  see comments

User · Answer

declare  RangeWidth int  set  RangeWidth   10  select    Floor Score  RangeWidth  as LowerBound     Floor Score  RangeWidth   RangeWidth as UpperBound     Count    From    ScoreTable group by    Floor Score  RangeWidth

User · Answer

An alternative approach would involve storing the ranges in a table  instead of embedding them in the query   You would end up with a table  call it Ranges  that looks like this   LowerLimit   UpperLimit   Range  0              9           0-9  10            19           10-19  20            29           20-29  30            39           30-39    And a query that looks like this   Select    Range as  Score Range      Count    as  Number of Occurences  from    Ranges r inner join Scores s on s Score between r LowerLimit and r UpperLimit group by Range   This does mean setting up a table  but it would be easy to maintain when the desired ranges change   No code changes necessary

User · Answer

select cast score 10 as varchar     -    cast score 10 9 as varchar           count    from scores group by score 10

User · Answer

I would do this a little differently so that it scales without having to define every case   select t range as  score range   count    as  number of occurences  from     select FLOOR score 10  as range   from scores  t group by t range   Not tested  but you get the idea

User · Answer

Perhaps you re asking about keeping such things going     Of course you ll invoke a full table scan for the queries and if the table containing the scores that need to be tallied  aggregations  is large you might want a better performing solution  you can create a secondary table and use rules  such as on insert - you might look into it   Not all RDBMS engines have rules  though

User · Answer

select t range as score  count    as Count  from         select UserId           case when isnull score  0   gt   0 and isnull score  0  lt  5 then  0-5                  when isnull score  0   gt   5 and isnull score  0  lt  10 then  5-10                  when isnull score  0   gt   10 and isnull score  0  lt  15 then  10-15                  when isnull score  0   gt   15 and isnull score  0  lt  20 then  15-20                          else   20   end as range           case when isnull score  0   gt   0 and isnull score  0  lt  5 then 1                 when isnull score  0   gt   5 and isnull score  0  lt  10 then 2                 when isnull score  0   gt   10 and isnull score  0  lt  15 then 3                 when isnull score  0   gt   15 and isnull score  0  lt  20 then 4                       else 5  end as pd      from score table        t  group by t range pd order by pd

User · Answer

In postgres  where    is the string concatenation operator    select  score 10  10     -      score 10  10 9 as scorerange  count    from scores group by score 10 order by 1   gives    scorerange   count  ------------ -------  0-9             11  10-19           14  20-29            3  30-39            2

User · Answer

declare  RangeWidth int  set  RangeWidth   10  select    Floor Score  RangeWidth  as LowerBound     Floor Score  RangeWidth   RangeWidth as UpperBound     Count    From    ScoreTable group by    Floor Score  RangeWidth

User · Answer

select t blah 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 blah   from scores  t group by t blah   Make sure you use a word other than  range  if you are in MySQL  or you will get an error for running the above example

User · Answer

An alternative approach would involve storing the ranges in a table  instead of embedding them in the query   You would end up with a table  call it Ranges  that looks like this   LowerLimit   UpperLimit   Range  0              9           0-9  10            19           10-19  20            29           20-29  30            39           30-39    And a query that looks like this   Select    Range as  Score Range      Count    as  Number of Occurences  from    Ranges r inner join Scores s on s Score between r LowerLimit and r UpperLimit group by Range   This does mean setting up a table  but it would be easy to maintain when the desired ranges change   No code changes necessary

User · Answer

Because the column being sorted on  Range  is a string  string word sorting is used instead of numeric sorting   As long as the strings have zeros to pad out the number lengths the sorting should still be semantically correct   SELECT t range AS ScoreRange         COUNT    AS NumberOfOccurrences   FROM  SELECT CASE                     WHEN score BETWEEN 0 AND 9 THEN  00-09                      WHEN score BETWEEN 10 AND 19 THEN  10-19                      ELSE  20-99                 END AS Range           FROM Scores  t  GROUP BY t Range   If the range is mixed  simply pad an extra zero   SELECT t range AS ScoreRange         COUNT    AS NumberOfOccurrences   FROM  SELECT CASE                     WHEN score BETWEEN 0 AND 9 THEN  000-009                      WHEN score BETWEEN 10 AND 19 THEN  010-019                      WHEN score BETWEEN 20 AND 99 THEN  020-099                      ELSE  100-999                 END AS Range           FROM Scores  t  GROUP BY t Range

User · Answer

select cast score 10 as varchar     -    cast score 10 9 as varchar           count    from scores group by score 10

User · Answer

Perhaps you re asking about keeping such things going     Of course you ll invoke a full table scan for the queries and if the table containing the scores that need to be tallied  aggregations  is large you might want a better performing solution  you can create a secondary table and use rules  such as on insert - you might look into it   Not all RDBMS engines have rules  though

User · Answer

I see answers here that won t work in SQL Server s syntax   I would use   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   EDIT  see comments

User · Answer

declare  RangeWidth int  set  RangeWidth   10  select    Floor Score  RangeWidth  as LowerBound     Floor Score  RangeWidth   RangeWidth as UpperBound     Count    From    ScoreTable group by    Floor Score  RangeWidth

User · Answer

I m here because i have similar question but i find the short answers wrong and the one with the continuous  quot case when quot  is to much work and seeing anything repetitive in my code hurts my eyes  So here is the solution SELECT --MIN score   MAX score        score range    CAST ROUND score-5 -1 AS VARCHAR      -     CAST  ROUND score-5 -1  10 AS VARCHAR        number of occurrences    COUNT    FROM order GROUP BY  CAST ROUND score-5 -1 AS VARCHAR      -     CAST  ROUND score-5 -1  10 AS VARCHAR  ORDER BY MIN score

User · Answer

create table scores      user id int     score int    select t range as  score range   count    as  number of occurences  from         select user id           case when score  gt   0 and score  lt  10 then  0-9           case when score  gt   10 and score  lt  20 then  10-19                        else  90-99  as range      from scores  t group by t range

User · Answer

I would do this a little differently so that it scales without having to define every case   select t range as  score range   count    as  number of occurences  from     select FLOOR score 10  as range   from scores  t group by t range   Not tested  but you get the idea

User · Answer

declare  RangeWidth int  set  RangeWidth   10  select    Floor Score  RangeWidth  as LowerBound     Floor Score  RangeWidth   RangeWidth as UpperBound     Count    From    ScoreTable group by    Floor Score  RangeWidth

User · Answer

Perhaps you re asking about keeping such things going     Of course you ll invoke a full table scan for the queries and if the table containing the scores that need to be tallied  aggregations  is large you might want a better performing solution  you can create a secondary table and use rules  such as on insert - you might look into it   Not all RDBMS engines have rules  though

[sql] In SQL, how can you "group by" in ranges?

Examples related to sql

Examples related to sql-server

Examples related to tsql