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','m')
insert into person(id,pname,Gender)values(3,'Anamika','m')
insert into person(id,pname,Gender)values(4,'raj','m')
insert into person(id,pname,Gender)values(5,'suhani','m')
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 ? | 2 Yes | 0 No |
Post New Answer View All Answers
When should you use an instead of trigger?
What is the use of keyword with encryption.
How to concatenate two strings in SQL Server.
Mention what are the different types of ssrs reports?
How will you make an attribute not process? : sql server analysis services, ssas
What happens if you add a new index to large table?
How to convert numeric expression data types using the cast() function?
what are the types of indexes? : Sql server database administration
Is sql server a database?
What is global temp table?
Tell me about builtinadministrator?
Difference between Inner vs outer joins?
What is the contrast between sql and mysql?
How to add more data to the testing table in ms sql server?
What is create command?