Friday, 14 June 2013

Group by in sql server

  Hi,
                    In this article we will see how to use simple group by clouse,group by with having,
rollup and compute.
Create below table and fill some data in that table .


Create table #ExamResult(name varchar(50),Subject varchar(20),Marks int)


insert into #ExamResult values('Adam','Maths',70)
insert into #ExamResult values ('Adam','Science',80)
insert into #ExamResult values ('Adam','Social',60)

insert into #ExamResult values('Rak','Maths',60)
insert into #ExamResult values ('Rak','Science',50)
insert into #ExamResult values ('Rak','Social',70)

insert into #ExamResult values('Sam','Maths',90)
insert into #ExamResult values ('Sam','Science',90)
insert into #ExamResult values ('Sam','Social',90)
insert into #ExamResult values ('Sam','Social',80)

step 1:: Simple group by Clouse


select name ,Subject,sum(Marks) total_mark from #ExamResult group by name,subject order by name,subject
step2:: Group by with having


select Subject,sum(Marks) total_mark from #ExamResult group by subject having subject='Social' order by subject
step3:: Group by with roll up


select name ,Subject,sum(Marks) total_mark from #ExamResult group by name,subject with rollup
step4:: Group by with compute function


select name ,Subject,Marks from #ExamResult
compute sum(Marks)

1 comment:

  1. What's up everyone, it's my first go to see at
    this site, annd post iss genuinely fruitful for me, keep up
    posting these types of artickes or reviews.


    Stop bby my homwpage :: TK (www.pendock.co.uk)

    ReplyDelete

8451831077