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