[RESOLVED] mssql Just to make sure on having count

[sapator]

Hi.

ex1

select Idx from tblx
group by Idx 
having count(Idx )> 1

That works

ex2

select Idx,id2 from tblx
group by Idx,id2
having count(Idx)> 1

That does not work (no rows returned)

ex3

select Idx,id2,count(*) from tblx
group by Idx,id2
having count(*)> 1

That works

So am I do understand that ex2 will not work, unless I do something crazy like:

select s.Idx , t.Id2

from

(

select Idx

from tblx

group by Idx

having count(*) > 1

)s

join Idx t

on s.Idx= t.Idx

?

[Zvoni]

ex1 and ex3 imply that
1) idx has duplicates
2) id2 has duplicates

as for ex2: Imagine a result like
idx - id2
1 - 1
1 - 2
1 - 3
1 - 4
and so on

You are grouping by idx AND id2
and the Having-Clause always goes on the GROUPED result
So where do you have a COUNT(idx)>1 in there?

i'm not sure, that ex3 actually returns the same results as ex1

What are you actually trying to accomplish?

[sapator]

It's more of a philosophical question.
Focusing on ex2, what you are telling me is that even if a having count(Idx)> 1 that will actually not matter and the group by will take precedence?

[Zvoni]

It's more of a philosophical question.
Focusing on ex2, what you are telling me is that even if a having count(Idx)> 1 that will actually not matter and the group by will take precedence?

Yes, GROUP BY takes precedence over Having.

Think the HAVING-CLAUSE like a WHERE-Clause for a grouped resultset
along the lines:

SELECT idx, MyCount FROM 
   (SELECT idx, COUNT(*) AS MyCount FROM MyTable GROUP BY idx)
WHERE MyCount>1

[sapator]

Thnx.

[LenDyems]

What is the significance of the GROUP BY clause taking precedence over HAVING in the context of the provided SQL queries?

[Zvoni]

It first GROUP's the records including aggregating, THEN applies the Filter in HAVING