what is the difference between group and having
give an example with query and sample output
Answers were Sorted based on User's Feedback
Answer / 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 |
Where: 'WHERE' will filter the data before grouping
Having:'HAVING' will filter,after the grouping the data.
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / soorai ganesh
create table lovepair (boyfrndname varchar(10),girlfrndname
varchar(10))
insert into lovepair values ('BF1','GF1')
insert into lovepair values ('BF1','GF2')
insert into lovepair values ('BF1','GF3')
insert into lovepair values ('BF2','GF1')
insert into lovepair values ('BF2','GF2')
insert into lovepair values ('BF3','GF3')
insert into lovepair values ('BF3','GF3')
insert into lovepair values ('BF3','GF3')
-- Here BF1 have 3 Girl Friends. like the BF2 and BF3 have
2 and 3 Girl Friends Simultaneously.
-- Here is the query for, how many girls friends each boys
have ???? USING group by
SELECT boyfrndname, COUNT (*)
FROM lovepair
GROUP BY boyfrndname
// Here another qry for who have more than 2 girl
friends ?? USING GroupBy and Having .
// Having is used for applying some condition in Aggregate
function
SELECT boyfrndname,COUNT(*)
FROM lovepair
Group BY boyfrndname
having count(*)>2
--- Now u clear...........
Is This Answer Correct ? | 4 Yes | 2 No |
Answer / dinesh sharma
Group by is used To Group Result According To Group Name
Having Is Always Is Used With Group by Clause.
Having Contain Aggregate Function or Simple Sub Query .
as Show In Above Example
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / 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 |
Answer / pawan378
in addition to the above correct answers ..
Having Clause allows to add conditions using functions
Ex: COUNT(*) > 2
at on the same phrase of the query. this will be basically
to avoid sub queries.
Is This Answer Correct ? | 0 Yes | 0 No |
- GROUP BY clause works on the rows returned by a SELECT Query. This clause summaries identical rows into a single/distinct group and returns a single row with the summary for each group, by using appropriate Aggregate function in the SELECT list, like COUNT(), SUM(), MIN(), MAX(), AVG(), etc.
- HAVING clause works as a Filter on top of the Grouped rows returned by the Query containing the GROUP BY clause. This clause cannot be replaced by a WHERE clause and vice-versa.
Check the difference b/w GROUP BY & HAVING, link: http://sqlwithmanoj.com/2015/05/23/sql-basics-difference-between-where-group-by-and-having-clause/
Is This Answer Correct ? | 0 Yes | 0 No |
How to apply cascading referential integrity in place of triggers?
What are the common performance issues in sql server?
in a table is b in column k (manikanta,sivananda,muralidhar) i want result like (mnikanta,sivnanda,murlidhar) please slove it
What is mean by dml?
How to retrieve duplicate rows in a table? How to delete the duplicate entries in a table?
15 Answers Leo Technologies, Mannar Company, Synechron,
What are the character string functions supported by sql server 2005?
How to fetch the next row from a cursor with a "fetch" statement?
Give an example of why you would want to denormalize a database
What does it mean to manipulate data?
Create a dts package to produce a text file using the ‘update statistics’ command for the tables in a database with obsolete statistics.
What is awe?
How to write a script for upate the data in prod , i have 50000 row are there