Rollup select statement problem

Question

Post reply

Rollup select statement problem

Quinn-793124

SSCrazy

Points: 2785
More actions
June 8, 2010 at 12:45 pm

#140734

I have a select statement that I can't get to work right.
select
case grouping(substring(permitnumb, 1, 3))
when 0 then case substring(permitnumb, 1, 3)
when 'K-7' then '2007'
when 'K-8' then '2008'
when 'K-9' then '2009'
when 'K9-' then '2009'
when 'K0-' then '2010'
end
when 1 then 'Grand Total'
end as Year,
sum(acres) as Acres
from timberpermits
group by substring(permitnumb, 1, 3)
with rollup;
Here is the result I get:
Year Acres
20102382.46254368
20074082.43477183
20083247.35429133
20092190.63280009
20091575.83025560
Grand Total13478.71466253
2009 has 2 different formats in the table I am summerizing for permitnumb in the table and I would like to be able to get them lumped together. The first 3 characters in permitnumb show what year they are from.
Thanks in advance.
Quinn

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

Please provide table definitions and sample data so we don't have to guess about those things.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

jjtetzlo SSCrazy Points: 2511 More actions · Answer 2

Here's an example with some trivial test data:

create table #timberpermits (permitnumb char(3), Acres float)

insert into #timberpermits

select 'K-7', 4082.43477183 union all

select 'K-8', 3247.35429133 union all

select 'K-9', 2190.63280009 union all

select 'K9-', 1575.83025560 union all

select 'K0-', 2382.46254368

select Year, sum(Acres) as Acres

from (

select

case grouping(substring(permitnumb, 1, 3))

when 0 then case

when substring(permitnumb, 1, 3) = 'K-7' then '2007'

when substring(permitnumb, 1, 3) = 'K-8' then '2008'

when substring(permitnumb, 1, 3) in ('K-9', 'K9-') then '2009'

when substring(permitnumb, 1, 3) = 'K0-' then '2010'

end

when 1 then 'Grand Total'

end as Year,

sum(acres) as Acres

from #timberpermits

group by substring(permitnumb, 1, 3)

with rollup

) x

group by Year

drop table #timberpermits

- Jeff

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 3

Thanks guys that worked great! I was just in the middle of getting the data when I got the second email. Should have included in the first place. Thanks

Quinn