Yousef has shown you how to handle this on the SQL side. On the .net side, you just need to write logic to populate the parameters when you call the SQL.
So if the user selects the "1-10" option, then you populate @From=1 and @To=10.
If the user selects "11-25", @From=11, @To=25.
And so on.
-Tab Alleman
Marked as answer by Chen Yu - MSFT on Jan 08, 2013 01:35 AM
First, maintain a master table for age groups, then use join to get count by age groups
Example
declare @agegroups table(
age_start int, age_end int, age_range varchar(20)
)
insert into @agegroups
select 1, 10, '1-10' union all
select 10, 25, '11-25' union all
select 25, 60, '26-60'
declare @table table (age int)
insert into @table
select 5 union all
select 10 union all
select 15 union all
select 20 union all
select 50 union all
select 60
select g.age_range, COUNT(1) as Count
from @table t
inner join @agegroups g on age between age_start and age_end
group by g.age_range
prontonet
Member
243 Points
484 Posts
Select Query that returns a Group of results
Dec 31, 2012 06:56 PM|LINK
hi,
I have a database with 500 people in of all ages from 1 to 65.
On my aspx page, I want offer a visitor to my site the chance to search
the people by age in groups.
so i would put up something l like this
age range 1- 10 click here
age range 10 -25 click here
age range 25 - 60 click here
then i will show the people that are in the range selected in a gridview
i am not sure how to put this sql query together if anyone can advise me/
thank you
Yousef_Jadal...
Star
8812 Points
1502 Posts
Re: Select Query that returns a Group of results
Dec 31, 2012 07:27 PM|LINK
You can send the ranges as parameters
This is part of the query :
prontonet
Member
243 Points
484 Posts
Re: Select Query that returns a Group of results
Dec 31, 2012 07:48 PM|LINK
hi, thanks
I want to decide the search range so the user has the option
to select a group rather than letting the User specify the range.
basically when they can click 4 buttons which will take them to a page
where the people in that group are presented.
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Select Query that returns a Group of results
Dec 31, 2012 08:50 PM|LINK
Yousef has shown you how to handle this on the SQL side. On the .net side, you just need to write logic to populate the parameters when you call the SQL.
So if the user selects the "1-10" option, then you populate @From=1 and @To=10.
If the user selects "11-25", @From=11, @To=25.
And so on.
oned_gk
All-Star
31511 Points
6431 Posts
Re: Select Query that returns a Group of results
Jan 01, 2013 02:31 AM|LINK
sandeepmitta...
Contributor
6789 Points
1058 Posts
Re: Select Query that returns a Group of results
Jan 01, 2013 05:06 AM|LINK
First, maintain a master table for age groups, then use join to get count by age groups
Example
Sandeep Mittal | My Blog - IT Developer Zone
Yousef_Jadal...
Star
8812 Points
1502 Posts
Re: Select Query that returns a Group of results
Jan 01, 2013 10:54 AM|LINK
You need to combine what I mentioned before with Tab Alleman frontend then you will solve your issue.
prontonet
Member
243 Points
484 Posts
Re: Select Query that returns a Group of results
Jan 01, 2013 01:23 PM|LINK
could you show the complete code here thanks?
prontonet
Member
243 Points
484 Posts
Re: Select Query that returns a Group of results
Jan 01, 2013 01:24 PM|LINK
great I will have a go at putting it together