Solved

Grouping SQL Results Based on certain ranges in an int field.MS SQL 2008

Posted on 2011-02-26
5
291 Views
Last Modified: 2012-05-11
I have a query which returns the results i need, will be putting this into a view as i'll need this for a few different things, one of the things i need it for is to group based on a particular field.

the field is called length, it is in kilometres, i want to group records based on the length as follows:

0-5 = group1
5-10 = group 2
10-20 = group 3
20-40 = group 4
>40 = group 5

could i use a case statement within the view to generate a column with the group name based on the ranges above, and then do a query on this view. grouping by that group column to collate the results?

to keep it simple please assume the following table:
id, roadname, city, state, roadlength

where roadlength is the column i want to group on.


0
Comment
Question by:HRT_HSV
5 Comments
 
LVL 9

Assisted Solution

by:sshah254
sshah254 earned 125 total points
ID: 34990258
Yes, you can use a case statement.

case
  when length between 0 and 5 then 'group1'
  when length between 6 and 10 then 'group2'
  ...
end case

Ss
0
 
LVL 18

Accepted Solution

by:
sventhan earned 125 total points
ID: 34991300
select roadname, city, state,
case
  when length between 0 and 5 then 'group1'
  when length between 6 and 10 then 'group2'
  when length between 11 and 20 then 'group3'
  when length between 21 and 40 then 'group4'
  when length>41 then 'group5' end
end as mygroup
from yourtable
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 34991404
Yes, both are right. If you want the view only for grouping purposes, and nothing else, you won't need that; you can use ad-hoc views (aka derived tables)
select kmgroup, min(roadlength), max(roadlength), avg(roadlength)
from
 (select *, 
    case when roadlength <=  5 then 'group1'
         when roadlength <= 10 then 'group2'
         when roadlength <= 20 then 'group3'
         when roadlength <= 40 then 'group4'
         else 'group5'
    end kmgroup
  from kmtable
 ) grptbl
group by kmgroup

Open in new window

In addition this example shows that you won't need to use the between in case.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34994096
yes you can use a case statement to do this but you would be much better off if you actually set up
a table and used that in your view to give the grouping/range name
0
 

Author Closing Comment

by:HRT_HSV
ID: 34996249
split points as first 2 comments provided the complete solution.  1st comment confirmed logic but 2nd comment had complete code.

Much appreciated everyone who provided solution thank you
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now