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.
| Author |
Topic |
|
byomjan
Starting Member
34 Posts |
 Posted - 2010-08-27 : 18:46:22
|
| create table dbo.student(Rollno int primary key identity, student_name varchar(30),House varchar(10),grade char(1) )Create table dbo.grade( grade char(1) ,description varchar(20))insert into dbo.studentselect 'John','Red', 'B'union allselect 'Shri','Red', 'C'union allselect 'Chow','Green', 'A'union allselect 'Ramesh','Green', 'A'union allselect 'Ken', 'Yellow','D'union allselect 'Anil','Yellow', 'A'Insert into dbo.gradeselect 'A','Above 90'unionselect 'B','Between 80 and 90'unionselect 'C','Between 70 and 80'unionselect 'D','below 70'Selectcase House when 'Red' Then 'Red' When 'Green' Then 'Green' When 'Yellow' Then 'Yellow' end as House , count (case when grade='A' then 1 end ) as 'Above 90', count (case when grade='B' then 1 end ) as 'Between 80 and 90', count (case when grade='C' then 1 end ) as 'Between 70 and 80', count (case when grade='D' then 1 end ) as 'below 70' From studentgroup by houseI am getting the desired result . But can i use any smarter function than case statements to count the grades ?Byomjan.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-28 : 01:11:13
|
| you can use PIVOT also to achieve. Look in books online for details.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Cross tab query