November 17, 2008 at 1:24 pm
Here is what I got, plus a slight mod towhat you provided.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[timberstands](
[acres] [smallint] NULL,
[curage] [smallint] NULL,
[ctype] [smallint] NULL
) ON [PRIMARY]
insert into [dbo].[timberstands]
SELECT '36','66','12' UNION ALL
SELECT '6','80','12' UNION ALL
SELECT '12','80','12' UNION ALL
SELECT '11','16','12' UNION ALL
SELECT '12','80','12' UNION ALL
SELECT '17','4','12' UNION ALL
SELECT '16','11','12' UNION ALL
SELECT '9','13','12' UNION ALL
SELECT '8','71','12' UNION ALL
SELECT '4','4','12' UNION ALL
SELECT '11','14','12' UNION ALL
SELECT '7','13','12' UNION ALL
SELECT '22','66','12' UNION ALL
SELECT '6','84','12' UNION ALL
SELECT '9','17','12' UNION ALL
SELECT '36','8','12' UNION ALL
SELECT '14','77','12' UNION ALL
SELECT '39','79','12' UNION ALL
SELECT '23','8','12' UNION ALL
SELECT '5','9','12' UNION ALL
SELECT '26','80','12' UNION ALL
SELECT '11','62','12' UNION ALL
SELECT '18','11','12' UNION ALL
SELECT '32','71','12' UNION ALL
SELECT '18','84','12' UNION ALL
SELECT '4','7','12' UNION ALL
SELECT '28','80','12' UNION ALL
SELECT '7','18','12' UNION ALL
SELECT '10','13','12' UNION ALL
SELECT '4','5','12' UNION ALL
SELECT '3','17','12' UNION ALL
SELECT '6','84','12' UNION ALL
SELECT '11','17','12' UNION ALL
SELECT '9','84','12' UNION ALL
SELECT '3','18','12' UNION ALL
SELECT '7','15','12' UNION ALL
SELECT '11','4','12' UNION ALL
SELECT '4','11','12' UNION ALL
SELECT '138','66','12' UNION ALL
SELECT '14','9','12' UNION ALL
SELECT '50','14','12' UNION ALL
SELECT '11','9','12' UNION ALL
SELECT '16','6','12' UNION ALL
SELECT '11','18','12' UNION ALL
SELECT '11','13','12' UNION ALL
SELECT '26','17','12' UNION ALL
SELECT '14','81','12' UNION ALL
SELECT '9','35','12' UNION ALL
SELECT '10','79','12' UNION ALL
SELECT '30','79','12' UNION ALL
SELECT '13','17','12' UNION ALL
SELECT '75','28','12' UNION ALL
SELECT '8','4','12' UNION ALL
SELECT '16','17','12' UNION ALL
SELECT '5','84','12' UNION ALL
SELECT '9','3','12' UNION ALL
SELECT '9','7','12' UNION ALL
SELECT '23','8','12' UNION ALL
SELECT '1','35','12' UNION ALL
SELECT '25','66','12' UNION ALL
SELECT '16','6','12' UNION ALL
SELECT '29','13','12' UNION ALL
SELECT '73','79','12' UNION ALL
SELECT '13','15','12' UNION ALL
SELECT '52','23','12' UNION ALL
SELECT '11','64','12' UNION ALL
SELECT '4','79','12' UNION ALL
SELECT '42','79','12' UNION ALL
SELECT '21','9','12' UNION ALL
SELECT '15','79','12' UNION ALL
SELECT '12','4','12' UNION ALL
SELECT '12','12','12' UNION ALL
SELECT '7','9','12' UNION ALL
SELECT '40','20','12' UNION ALL
SELECT '11','4','12' UNION ALL
SELECT '6','79','12' UNION ALL
SELECT '14','13','12' UNION ALL
SELECT '5','8','12' UNION ALL
SELECT '5','8','12' UNION ALL
SELECT '4','4','12' UNION ALL
SELECT '7','15','12' UNION ALL
SELECT '19','4','12' UNION ALL
SELECT '14','66','12' UNION ALL
SELECT '2','1','12' UNION ALL
SELECT '12','4','12' UNION ALL
SELECT '6','3','12' UNION ALL
SELECT '15','77','12' UNION ALL
SELECT '3','77','12' UNION ALL
SELECT '39','2','12' UNION ALL
SELECT '16','15','12' UNION ALL
SELECT '18','25','12' UNION ALL
SELECT '14','13','12' UNION ALL
SELECT '19','15','12' UNION ALL
SELECT '6','14','12' UNION ALL
SELECT '6','14','12' UNION ALL
SELECT '12','3','12' UNION ALL
SELECT '6','4','12' UNION ALL
SELECT '29','77','12' UNION ALL
SELECT '25','80','12' UNION ALL
SELECT '6','77','12' UNION ALL
SELECT '16','81','12' UNION ALL
SELECT '5','4','12' UNION ALL
SELECT '7','77','12' UNION ALL
SELECT '8','4','12' UNION ALL
SELECT '3','77','12' UNION ALL
SELECT '8','79','12' UNION ALL
SELECT '25','16','12' UNION ALL
SELECT '20','6','12' UNION ALL
SELECT '21','76','12' UNION ALL
SELECT '2','20','12' UNION ALL
SELECT '11','20','12' UNION ALL
SELECT '58','31','12' UNION ALL
SELECT '7','81','12' UNION ALL
SELECT '55','67','12' UNION ALL
SELECT '22','6','12' UNION ALL
SELECT '10','25','12' UNION ALL
SELECT '9','9','12' UNION ALL
SELECT '12','80','12' UNION ALL
SELECT '21','6','12' UNION ALL
SELECT '16','3','12' UNION ALL
SELECT '23','6','12' UNION ALL
SELECT '37','17','12' UNION ALL
SELECT '32','18','12' UNION ALL
SELECT '14','5','12' UNION ALL
SELECT '19','13','12' UNION ALL
SELECT '15','6','12' UNION ALL
SELECT '18','79','12' UNION ALL
SELECT '8','76','12' UNION ALL
SELECT '6','34','12' UNION ALL
SELECT '5','15','12' UNION ALL
SELECT '12','6','12' UNION ALL
SELECT '2','3','12' UNION ALL
SELECT '4','74','12' UNION ALL
SELECT '51','76','12' UNION ALL
SELECT '4','76','12' UNION ALL
SELECT '9','76','12' UNION ALL
SELECT '11','34','12' UNION ALL
SELECT '14','17','12' UNION ALL
SELECT '22','9','12' UNION ALL
SELECT '6','9','12' UNION ALL
SELECT '18','32','12' UNION ALL
SELECT '8','80','12' UNION ALL
SELECT '20','11','12' UNION ALL
SELECT '9','32','12' UNION ALL
SELECT '4','30','12' UNION ALL
SELECT '10','74','12' UNION ALL
SELECT '14','15','12' UNION ALL
SELECT '7','22','12' UNION ALL
SELECT '3','30','12' UNION ALL
SELECT '8','15','12' UNION ALL
SELECT '7','82','12' UNION ALL
SELECT '6','5','12'
;with Ages (
acres,
AgeClass
) as (
select
acres,
case
when curage <= 5 then 'Age 0 - 5'
when curage >= 6 and curage <= 10 then 'Age 6 - 10'
when curage >= 11 and curage <= 15 then 'Age 11 - 15'
when curage >= 16 and curage <= 20 then 'Age 16 - 20'
when curage >= 21 and curage <= 25 then 'Age 21 - 25'
when curage >= 26 and curage <= 30 then 'Age 26 - 30'
when curage >= 31 and curage <= 35 then 'Age 31 - 35'
when curage >= 36 and curage <= 40 then 'Age 36 - 40'
when curage >= 41 and curage <= 45 then 'Age 41 - 45'
when curage >= 46 and curage <= 50 then 'Age 46 - 50'
when curage > 50 then 'Age 50+'
else 'Big Problem'
end as AgeClass
from
timberstands
where
ctype = 12
)
select
AgeClass,
sum(acres)
from
Ages
group by
AgeClass;
DROP TABLE [dbo].[timberstands]
AgeClass(No column name)
Age 0 - 5 227
Age 6 - 10 345
Age 11 - 15 345
Age 16 - 20 271
Age 21 - 25 87
Age 26 - 30 82
Age 31 - 35 112
Age 50+ 998
November 17, 2008 at 1:26 pm
Quinn (11/17/2008)
I need to summarize acres by ageclass.Thanks again.
Q
I know WHAT you want, but I was asking what should the results BE based on the sample data provided. You will have to check the results I got and posted above.
November 17, 2008 at 1:38 pm
Yes
Use the data in the format provided.
Q
November 17, 2008 at 1:40 pm
Quinn (11/17/2008)
YesUse the data in the format provided.
Q
You are still missing the point. Is the DATA correct? That is what I was asking when I asked for the expected results based on the sample data.
November 17, 2008 at 1:46 pm
Sorry about that.
Yes the data is correct.
November 18, 2008 at 9:30 am
Lynn
I was able to get both commands going this morning. I didn't copy far enough down on your CTE solution.
Thanks for all of your patience.
Quinn
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply