what is the difference between group and having
give an example with query and sample output
Answer Posted / krishna murari chaubey
You Have two table person and friend
create table person(id int,pname varchar(20),gender varchar(3))
insert into person(id,pname,Gender)values(1,'krishna','m')
insert into person(id,pname,Gender)values(2,'Radha','f')
insert into person(id,pname,Gender)values(3,'Anamika','f')
insert into person(id,pname,Gender)values(4,'raj','m')
insert into person(id,pname,Gender)values(5,'suhani','f')
insert into person(id,pname,Gender)values(6,'ravi','m')
create table friend(id int,fid int)
insert into friend(id,fid)values(1,2)
insert into friend(id,fid)values(1,3)
insert into friend(id,fid)values(1,5)
insert into friend(id,fid)values(2,3)
insert into friend(id,fid)values(1,4)
insert into friend(id,fid)values(1,6)
insert into friend(id,fid)values(6,2)
insert into friend(id,fid)values(6,3)
insert into friend(id,fid)values(3,2)
insert into friend(id,fid)values(3,2)
insert into friend(id,fid)values(3,1)
find person who is male and having more than two female friend
Asnswer : -
select id,count(fid) as numberOfFemaleFriend from friend where fid in(select id from person where gender='f')
and id in (select id from person where gender='m' )
group by id having count(fid) >2
OR You can use Inner Join
select f.id,p.pname,count(f.fid) as numberOfFemaleFriend
from person p
inner join friend f
on p.id=f.id and p.gender='m' and f.fid in
(select id from person where gender='f')
group by f.id,p.pname having(count(f.fid)>2)
Is This Answer Correct ? | 4 Yes | 0 No |
Post New Answer View All Answers
What is single-user mode and what are the steps you should follow to start sql server in single-user mode?
What are the 7 disadvantages to a manual system?
What is a trigger what are the advantages of trigger?
What are transactions in sql?
What are the different types of locks in the database?
What are pages and extents? : SQL Server Architecture
List out what other servers you can use with ssrs?
Do you know hot add cpu in sql server 2008?
If no size is defined while creating the database, what size will the database have?
Do you know what are acid properties?
What is the difference between a local and a global temporary table?
What is a dbms wizard?
how to use DTS package in 2000,2005,2008 in sql server
Explain different forms of normalization?
how you can deploy an ssrs report?