Home » SQL & PL/SQL » SQL & PL/SQL » Count Query in Having Clause
Count Query in Having Clause [message #286882] Mon, 10 December 2007 06:16 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
The below given is my query.can anyone explain how the count(*) will work in the having clause

select a.id,b.name,c.dept,d.mgr,d.sal
from emp a,empn b,deptm c,det d
where a.cus_id=b.cus_id and
      c.cus_id=a.cus_id and
      c.key='MGR' and
      d.cus_id=a.cus_id and
      d.key='DET'
group by a.id,b.name,c.dept,d.mgr,d.sal
having count(*)>1
Re: Count Query in Having Clause [message #286894 is a reply to message #286882] Mon, 10 December 2007 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference
SELECT page
HAVING Clause

Regards
Michel
Re: Count Query in Having Clause [message #287002 is a reply to message #286894] Mon, 10 December 2007 20:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It will filter out combinations of a.id,b.name,c.dept,d.mgr,d.sal for which there is just one row in the result set.

Ross Leishman
Re: Count Query in Having Clause [message #287203 is a reply to message #287002] Tue, 11 December 2007 06:59 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
for which there is just one row in the result set



For just one row or more than one row?
If one can you please explain how it will return just one row from the result set.i have given count(*)>1
Re: Count Query in Having Clause [message #287206 is a reply to message #287203] Tue, 11 December 2007 07:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'd read the link Michel has posted. Really, it is interesting and to the point.

In this case: For each group, the count will be done. If that count is greater than 1 (i.e. there is more than one record in the group), the record will pass. If there is only one record in the group, the group will be rejected.

MHE
Re: Count Query in Having Clause [message #287353 is a reply to message #287206] Wed, 12 December 2007 01:13 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Thanks for all now i am clear.

The link posted by Michel is really very helpful to me thanks.

Re: Count Query in Having Clause [message #287354 is a reply to message #287353] Wed, 12 December 2007 01:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Just out of curiosity, but why do you need Maaher to explicitly point you to the link before you actually read it? Michel put it there for a reason.
Re: Count Query in Having Clause [message #287367 is a reply to message #287354] Wed, 12 December 2007 01:42 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Because I'm the pretty one. People tend to listen when I blink with my beautiful eyes. Very Happy

MHE
Previous Topic: Time Increment
Next Topic: Query Problem (Not returning required result)
Goto Forum:
  


Current Time: Sat Dec 10 10:51:17 CST 2016

Total time taken to generate the page: 0.11241 seconds