Click here to Skip to main content
15,878,543 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i have a very simple problem with select query . I Have table having Column id and name

IDName
1abc
1abc
1xze
1qwe
2xyz
2adf
1jkl

i want to show id having >=4 with there name also.


SQL
select name ,id ,COUNT(id) from demo group by id ,name  having COUNT(id)>=4


the query is executing perfectly but does not return anything .It shows Empty Rows. WHY??

Posted
Updated 11-Mar-13 9:30am
v2
Comments
Richard C Bishop 11-Mar-13 10:54am    
So the query is not exexuting perfectly, it fails.
addy908 11-Mar-13 11:12am    
so how to get reqired result

Hi there,

I suspect you want to do

SQL
SELECT ID, COUNT(Name) HAVING COUNT(NAME) > 4 
 
Share this answer
 
Comments
addy908 11-Mar-13 11:40am    
no u didn't get it ..i want to show id , name having same id but count of id >=4
Try:
SQL
SELECT 
  name ,id 
FROM
  demo 
GROUP BY
  id
HAVING 
  COUNT(id) >= 4
 
Share this answer
 
Comments
addy908 11-Mar-13 11:41am    
I also have done the same but It give me an error .
Sandeep Mewara 11-Mar-13 12:02pm    
What error?

Here is the documented sample: http://www.tutorialspoint.com/sql/sql-having-clause.htm
addy908 11-Mar-13 12:03pm    
Column 'demo.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Sandeep Mewara 11-Mar-13 12:07pm    
Ok, add that in groupby clause.
addy908 11-Mar-13 12:09pm    
then it shows empty rows .Query executed successfully .
Hi,

Try this script....

SQL
SELECT DISTINCT M.Id, M.Name, T.IDsCount 
FROM demo M
INNER JOIN (SELECT id, COUNT(id) 'IDsCount' 
			FROM demo 
			GROUP BY id
			HAVING COUNT(id) >= 4) T ON T.IDsCount=M.id

Regards,
GVPrabu
 
Share this answer
 
Hi,

Use this:

SELECT ID,NAME FROM Codeproj WHERE ID IN(SELECT id FROM Codeproj GROUP BY id HAVING Count(ID)>4)
 
Share this answer
 
Comments
addy908 12-Mar-13 2:24am    
Thanks david .!! can u tell me why this query is not retuning any row .
select name ,id ,COUNT(id) from demo group by id ,name having COUNT(id)>=4
Davidduraisamy 12-Mar-13 2:39am    
You made group by on both id,name so in this scenario having will handle with both id and name so chek this u will come to know the difference

select name ,id from demo group by id ,name having COUNT(id)>=2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900