count query question - newbie - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

count query question - newbie

Author

Topic

cranky franky

12 Posts

Posted - 2009-03-21 : 21:11:20

newbie with basic knowledge...running research on a large resort DB and need what appears to be expert help.

I have a schedule/calendar table with numerous columns including lastname, lessontype and wish to count all the lessontypes for distinct lastnames over a date range. I wish to have each distinct lessontype appear in its own column in the results, with the total of each type for each distinct lastname.
example data with only 2 lesson types:
Lastname lessontype
Williams beginner
Smith intermediate
Jones beginner
Williams beginner
Smith beginner
Williams beginner
Jones intermediate
Smith intermediate

Desired Result view
Lastname beginner intermediate
Jones 1 1
Smith 1 2
Williams 3 0

I've attempted to use aliases, multiple where clauses, etc but can't seem to make this work. I can easily run counts for a single lesson type by lastname...that is no help. I want a "grid" result set showing columns for all lessontypes and distinct lastnames. I could have an unlimited number of lessontypes I'm wishing to count but usually no more than about 6. Thanks for any help!!

Franklin

cloud
Starting Member

1 Post

Posted - 2009-03-23 : 12:30:41

Try

create table #schedule
(LastName varchar(20),
lessontype varchar(20) )

insert into #schedule values('Williams','beginner')
insert into #schedule values('Smith','intermediate')
insert into #schedule values('Jones','beginner')
insert into #schedule values('Williams','beginner')
insert into #schedule values('Smith','beginner')
insert into #schedule values('Williams','beginner')
insert into #schedule values('Jones','intermediate')
insert into #schedule values('Smith','intermediate')

Select LastName,sum(Beginner) Beginner, sum(Intermediate) Intermediate
From
(
Select
LastName,
Case when lessontype='beginner' then 1 else 0 end Beginner,
Case when lessontype='intermediate' then 1 else 0 end Intermediate
From #schedule) as d
Group by LastName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-23 : 12:53:32

this is script libraray and is intended for posting working scripts. so in future please post this type of questions in transact sql or new to sql forums.

cranky franky

12 Posts

Posted - 2009-03-24 : 13:58:54

Will do. Sorry about that! Thanks for help

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-03-24 : 14:43:20

moved from script library

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources