2

I've created the following query for calculating the number of repetitions of an ID in a table.

select count(Hazaa) as Countie from Shazoo
group by Hazaa

Realizing that most of them are equal to 1 (as they should be), I added the condition to only show the faulty ones. However, SQL Server disagrees with me regarding the syntax.

select count(Hazaa) as Countie from Shazoo
where Countie > 1
group by Hazaa

How can I discriminate my results with respect to the number of them?

Konrad Viltersten
  • 36,151
  • 76
  • 250
  • 438
  • 1
    possible duplicate of [SQL - WHERE AGGREGATE>1](http://stackoverflow.com/questions/1213004/sql-where-aggregate1) – AHiggins Sep 09 '15 at 11:56
  • 1
    possible duplicate of [Select where count of one field is greater than one](http://stackoverflow.com/questions/3710483/select-where-count-of-one-field-is-greater-than-one) – Ilmari Karonen Sep 09 '15 at 15:19

1 Answers1

7

For filtering result of GROUP BY you need to use HAVING 

SELECT Hazaa, COUNT(Hazaa) AS Countie
FROM Shazoo
GROUP BY Hazaa
HAVING COUNT(Hazaa) > 1

or wrap it in subquery/CTE and filter with WHERE:

;WITH cte AS (
   SELECT Hazaa, COUNT(Hazaa) AS Countie
   FROM Shazoo
   GROUP BY Hazaa
)
SELECT *
FROM cte
WHERE Countie > 1;

See simplified order of execution

enter image description here

Read simplified image like: first take data from sources, then filter using WHERE, then GROUP BY, then filter group result using HAVING and in the end get/project column with SELECT.

You can think also this way, WHERE/HAVING will filter your rows but with SELECT you will limit the columns like: SELECT * FROM tab gives all of them and SELECT id FROM tab gives only one.

Logical query processing

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • I think `HAVING()` serve the purpose far better then workaround like CTE – Eric Sep 09 '15 at 11:41
  • @Eric This is only to show author that `WHERE` can work too. Of course `HAVING` is vastly superior here. – Lukasz Szozda Sep 09 '15 at 11:42
  • 2
    @Eric I would also say that if the aggregate condition is more than simple ( like sum(case when...), there is an advantage to the CTE in that you don't have to copy the long string over and over. Makes it easier to read in come cases. – Jeremy Sep 09 '15 at 11:51
  • This was right spot on **but** I only can give it +1, which is **waaay** too little. The image is awesome. (I hope you had one already. Otherwise, you seem to have way too much spare time, hahaha. Thanks!) However, I wonder why the *select* is smaller than the conditions providing it. **Can** it limit the set being selected? – Konrad Viltersten Sep 09 '15 at 11:52
  • @KonradViltersten This image may be misleading. I mean that `SELECT` is one of the last operation when executed (but when you write query it is first). If you know `LINQ` you will see more natural order `from where select` See [this poster](http://tsql.solidq.com/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf) – Lukasz Szozda Sep 09 '15 at 11:59
  • @KonradViltersten Read simplified image like: first take data from sources, then filter using `WHERE` then `GROUP`, then filter group result using `HAVING` and in the end get/project column with `SELECT` – Lukasz Szozda Sep 09 '15 at 12:00
  • @KonradViltersten You can think also this way, `WHERE/HAVING` will limit/filter your rows but with `SELECT` you will limit the columns like: `SELECT * FROM tab` gives all of them and `SELECT id FROM tab` gives only one. – Lukasz Szozda Sep 09 '15 at 12:05
  • That image from your comment needs definitely to go into the answer body. As do the comments themselves. And yes, I do use LINQ and (almost) exclusively with the inline syntax, where select is done to project the information into the entity that's going to be used. So I understand perfectly what you mean. Very good reply! – Konrad Viltersten Sep 09 '15 at 13:18