Help with grouping query

[TysonLPrice]

I always have trouble with this kind of query. I'm trying to count the number of occurences of a column with a given value in a range. The data is:

create table ##Steve(Year varchar(4),flag char(1))
insert into ##Steve(year,flag) values('2000','A')
insert into ##Steve(year,flag) values('2000','C')
insert into ##Steve(year,flag) values('2000','B')
insert into ##Steve(year,flag) values('2001','A')
insert into ##Steve(year,flag) values('2001','B')
insert into ##Steve(year,flag) values('2001','B')
insert into ##Steve(year,flag) values('2001','C')
insert into ##Steve(year,flag) values('2002','A')
insert into ##Steve(year,flag) values('2002','A')
insert into ##Steve(year,flag) values('2002','B')
insert into ##Steve(year,flag) values('2002','C')

the expected output is:

Year A B C
2001 1 2 1
2002 2 1 1

I've been trying pivot, cube and rollup but I'm getting no where. This was my last attempt to show what I am trying:

select year,
count(case when flag = 'A' then flag end) as a,
count(case when flag = 'B' then flag end) as b,
count(case when flag = 'C' then flag end) as c
from ##Steve
where year between '2001' and '2002'
group by year,flag with rollup

It is not what I'm looking for.

 

[MarkSweetland]

Try this:

declare @Steve TABLE (Year varchar(4),flag char(1))
set nocount on 
insert into @Steve(year,flag) values('2000','A')
insert into @Steve(year,flag) values('2000','C')
insert into @Steve(year,flag) values('2000','B')
insert into @Steve(year,flag) values('2001','A')
insert into @Steve(year,flag) values('2001','B')
insert into @Steve(year,flag) values('2001','B')
insert into @Steve(year,flag) values('2001','C')
insert into @Steve(year,flag) values('2002','A')
insert into @Steve(year,flag) values('2002','A')
insert into @Steve(year,flag) values('2002','B')
insert into @Steve(year,flag) values('2002','C')

select YEAR, a=SUM(a), b=SUM(b), c=SUM(c)
from 
(
	select year, 
	case when flag = 'A' then 1 else 0 end as a, 
	case when flag = 'B' then 1 else 0 end as b,
	case when flag = 'C' then 1 else 0 end as c
	from @Steve
	where year between '2001' and '2002'
	) as d
group by year

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[gmmastros]

[tt]
select year,
count(case when flag = 'A' then flag end) as a,
count(case when flag = 'B' then flag end) as b,
count(case when flag = 'C' then flag end) as c
from ##Steve
where year between '2001' and '2002'
group by year,flag with rollup
[/tt]

This was close. Very Close.

select year,
       count(case when flag = 'A' then flag end) as a, 
       count(case when flag = 'B' then flag end) as b,
       count(case when flag = 'C' then flag end) as c
from   ##Steve
where  year between '2001' and '2002'
group by year

I removed the last part [!],flag with rollup[/!]

Look at it this way... When you group by year, you're basically saying I want 1 row for each year. When you group by Year, Flag you are basically saying, I want 1 row in the output for each distinct combination of Year and Flag.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TysonLPrice]

Thanks...I was making it harder then it needed to be. I get the same results with:

SELECT year, [A], , [C]
FROM ##Steve
p
PIVOT
(
COUNT (flag)
FOR flag
IN ([A], , [C])
) AS pvt;

Problem is that syntax is for SQL 2005. We are on 2005 but that campatibility is set to 2000. There is one developement DB at 2005 and it works there. On the 2000 versions I get this error.

Msg 325, Level 15, State 1, Line 4
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

 

[gmmastros]

The code that Mark and I posted will work with SQL2000.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TysonLPrice]

Yes that's what I'm going with. I got the pivot working at the same time the answers got posted. Too bad about the compatibility issue.

When 2005 was installed here the compatibilty level was set to 2000 in case there were stored procedures that would break. The DBA group was in a real time crunch. Downside is some new features can't be used.