November 1, 2005 at 2:34 pm
Hello,
Please if someone has an idea to the following
I have a table as :
Yr Total Ag
1990 15 19
1990 30 50
1990 40 30
1995 30 25
1995 60 6
1995 10 52
I want to make it as following:
Yr Total Grp
1990 15 1-20
1990 70 21-55
1995 60 1-20
1995 40 21-55
The Grp column is the range of the Ag column.
Thanks
November 1, 2005 at 2:40 pm
I'm not really sure what you are asking for? Are you looking at renaming the Ag column to Grp and converting the data over? Or is Grp going to be a new column in your table?
November 1, 2005 at 2:47 pm
It's a new column which will group the Ag values like from 1 To 20 then the total will be Sum for that range based on the Year.
For the range of 1 -20 and for the Yr of 1990 we have only one value which is 15 therfore it dispalys as
1990 15 1-20
And so on .
November 1, 2005 at 2:53 pm
So are you looking for the T-SQL syntax to initialy populate this new column? I would write a case statement to check and see where all of your ag values fall within whatever ranges you have in mind and populate the grp column accordingly.
November 1, 2005 at 3:08 pm
To get the results the way you want you;ll need to union.
select Yr, sum(Case when AG between 1 and 20 then Total end) as 'Total', '1-20' as GRP
from testtable
group by yr
union
select Yr, sum(Case when AG between 21 and 55 then Total end) as 'Total', '21-55'
from testtable
group by yr
order by Yr,GRP
But it looks better like this
select Yr, sum(Case when AG between 1 and 20 then Total end) as '1-20',
sum(Case when AG between 21 and 55 then Total end) as '21-55'
from testtable
group by yr
Results
Yr, 1-20, 21-55
1990, 15, 70
1995, 60, 40
November 1, 2005 at 3:38 pm
Hi,
I did it using a temp table that contained the original fields plus a field that had a range as a string named AgeGroup. Then I just grouped by AgeGroup in another statement in the same query.
Select Field1, Field2, Field3, Age
,case
when Age <35 then '< 35'
when (Age >=35 AND Age < 45) then '35-44'
when (Age >=45 AND Age <55) then '45-54'
when (Age >=55 AND Age <65) then '55-64'
when (Age >=65 AND Age <75) then '65-74'
when (Age >=75) then '75+'
when Age is Null then 'No Response'
else 'No Response'
end
as AgeGroup
Into ##TempTable2
from OriginalTable
----------------------------------
<Select Statement towards ##TempTable2 with grouping by AgeGroup goes here>
Regards,Yelena Varsha
November 2, 2005 at 3:41 am
What about this - no hard coding of values and should be easily adapted to many different situations...
declare @ranges table(minRange int, maxRange int) insert into @ranges(minRange, maxRange) select 1, 20 UNION select 21, 55 UNION select 56, 100
declare @data table(yr int, total int, ag int) insert into @data(yr, total, ag) select 1990, 15, 19 UNION select 1990, 30, 50 UNION select 1990, 40, 30 UNION select 1995, 30, 25 UNION select 1995, 60, 6 UNION select 1995, 10, 52
select yr as yr, sum(total) as total, cast(minRange as varchar(5)) + '-' + cast(maxRange as varchar(5)) as grp from @data D inner join @ranges R on D.ag between R.minRange and R.maxRange group by yr, minRange, maxRange
November 2, 2005 at 3:42 am
BTW... output is
yr total grp ----------- ----------- ----------- 1990 15 1-20 1990 70 21-55 1995 60 1-20 1995 40 21-55
November 2, 2005 at 7:17 am
Thanks a lot for all of you.
Regards.
November 2, 2005 at 8:55 am
Select
Yr,
(Case
When Ag <=20 Then '1-20'
When (Ag >=21 AND Ag <= 55) Then '21-55'
When Age >=56 Then '56+'
End) as Grp,
Sum(Total) as Total
From yourTable
Group By
Yr,
(Case
When Ag <=20 Then '1-20'
When (Ag >=21 AND Ag <= 55) Then '21-55'
When Age >=56 Then '56+'
End)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy