[RESOLVED] mssql Just to make sure on having count

Hi.

ex1

Code:

---

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

---

That works

ex2

Code:

---

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

---

That does not work (no rows returned)

ex3

Code:

---

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:

Code:

---


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

---

?

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?

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?

Quote:

---

Originally Posted by 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?

---

Yes, GROUP BY takes precedence over Having.

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

Code:

---

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

---

Thnx.

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

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