MS sql Query problem

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

i have a very simple problem with select query . I Have table having Column id and name

ID	Name
1	abc
1	abc
1	xze
1	qwe
2	xyz
2	adf
1	jkl

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 11-Mar-13 4:50am

addy908

Updated 11-Mar-13 9:30am

Jörgen Andersson

v2

Add a Solution

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

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

FrancoisViljoen · Answer 1 · 2013-03-11T05:22:00

Solution 1

Hi there,

I suspect you want to do

SQL

SELECT ID, COUNT(Name) HAVING COUNT(NAME) > 4

Posted 11-Mar-13 5:22am

FrancoisViljoen

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

Sandeep Mewara · Answer 2 · 2013-03-11T05:29:00

Solution 2

Try:

SQL

SELECT 
  name ,id 
FROM
  demo 
GROUP BY
  id
HAVING 
  COUNT(id) >= 4

Posted 11-Mar-13 5:29am

Sandeep Mewara

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 .

addy908 11-Mar-13 12:11pm

i want to know why it is not returning any rows ..

gvprabu · Answer 3 · 2013-03-11T06:48:00

Solution 3

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

Posted 11-Mar-13 6:48am

gvprabu

Davidduraisamy · Answer 4 · 2013-03-11T18:48:00

Solution 4

Hi,

Use this:

SELECT ID,NAME FROM Codeproj WHERE ID IN(SELECT id FROM Codeproj GROUP BY id HAVING Count(ID)>4)

Posted 11-Mar-13 18:48pm

Davidduraisamy

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