November 17, 2008 at 9:42 am
When have this query that works fine that I want to add sum() and distint() to but when I do I get an error.
Here is the Query.
select acres, case
when curage <= 5 then 'Age 0-5'
when curage >= 5 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
When I add sum(acres), distinct(AgeClass) and group by ageclass I get an error that says there is a problem by the distinct.
Is there a way to do this query?
Thank you in advance.
Quinn
November 17, 2008 at 9:53 am
could you please post the whole error message:-)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 17, 2008 at 9:58 am
Chris,
Here is the query and the error.
Thanks.
Quinn
select sum(acres), case
when curage <= 5 then 'Age 0-5'
when curage >= 5 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 distinct(AgeClass)
from timberstands
where ctype = 12
group by ageclass
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'distinct'.
November 17, 2008 at 10:01 am
Christopher Stobbs (11/17/2008)
could you please post the whole error message:-)
Some sample data from the table "timberstands" would be very handy too!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 17, 2008 at 10:02 am
looks like your distinct is in the wrong place.
Try this:
select DISTINCT sum(acres), case
when curage <= 5 then 'Age 0-5'
when curage >= 5 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
group by ageclass
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 17, 2008 at 10:11 am
Hi Chris, don't mean to tread on your toes but the group by requires the whole expression used to generate the new column, rather than the name of the new column. Which gives this big ugly mess:
[font="Courier New"]SELECT SUM(acres), CASE
WHEN curage <= 5 THEN 'Age 0-5'
WHEN curage >= 6 AND curage <= 10 THEN 'Age 6-10' -- correction
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
GROUP BY CASE
WHEN curage <= 5 THEN 'Age 0-5'
WHEN curage >= 6 AND curage <= 10 THEN 'Age 6-10' -- correction
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
[/font]
It will probably run, but would run almost as quickly and look far more elegant if an initial aggregate were wrapped in a final "presentation" aggregate.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 17, 2008 at 10:13 am
Try this:
;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;
Using a CTE (this is a SQL Server 2005 forum) makes it cleaner.
November 17, 2008 at 10:14 am
don't worry my toes are all good 🙂
Yeah was just thinking that creating it in a CTE no summing first then grouping the cte on the new column based on the GROUP BY mentioned would prob look much better and save human error changes later on .
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 17, 2008 at 10:14 am
There you have it Lynn beat me to the code post 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 17, 2008 at 10:21 am
Christopher Stobbs (11/17/2008)
There you have it Lynn beat me to the code post 🙂
You had it on the nose though! I'd still like to see the data, reckon there's something missing here...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 17, 2008 at 10:39 am
So the final cokmmand is this?
SELECT SUM(acres), CASE
WHEN curage <= 5 THEN 'Age 0-5'
WHEN curage >= 6 AND curage <= 10 THEN 'Age 6-10' -- correction
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
GROUP BY CASE
WHEN curage <= 5 THEN 'Age 0-5'
WHEN curage >= 6 AND curage <= 10 THEN 'Age 6-10' -- correction
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'
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
Thanks
Quinn
November 17, 2008 at 11:17 am
What you have posted is a mix of both possible solutions.
I think most of us are in agreement that the following may be the best solution, but that we'd still like to have sample data to test the solution.
;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;
Please note that there is a scroll bar so there is more code to see.
November 17, 2008 at 1:02 pm
Lynn and all,
I hope I get this right.
Here is the data. It is a paired down set of data.
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]
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'
Thanks again.
Quinn
November 17, 2008 at 1:17 pm
Based on the sample data, what is your expected results?
November 17, 2008 at 1:21 pm
I need to summarize acres by ageclass.
Thanks again.
Q
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply