Building sql query with count() where count() is > 1

You need to understand the logical query processing phases. Following are the main query clauses specified in the order that you are supposed to type them (known as “keyed-in order”):

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

The logical query processing order, which is the conceptual interpretation order, is different. It starts with the FROM clause. Here is the logical query processing order of the six main query clauses:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

A typical mistake made by people who don’t understand logical query processing is attempting to refer in the WHERE clause to a column alias defined in the SELECT clause. You can’t do this because the WHERE clause is evaluated before the SELECT clause.

If you understand that the WHERE clause is evaluated before the SELECT clause, you realize that this attempt is wrong because at this phase, the attribute myCount doesn’t yet exist.

It’s important to understand the difference between WHERE and HAVING. The WHERE clause is evaluated before rows are grouped, and therefore is evaluated per row.

The HAVING clause is evaluated after rows are grouped, and therefore is evaluated per group. The HAVING (evaluated per group):

  • can contain aggregate functions
  • executed after grouping (exclude records after grouping)
  • cannot be used without a GROUP BY

In the other hand, the WHERE :

  • cannot contain aggregate functions (like in your case)
  • processes after FROM
  • can be used without GROUP BY

So your query should be like below : 

SELECT COUNT(ID) AS myCount FROM myTbl
GROUP BY ID
HAVING COUNT(ID) > 1

Note : Notice that the ORDER BY clause is the first and only clause that is allowed to refer to column aliases defined in the SELECT clause. That’s because the ORDER BY clause is the only one to be evaluated after the SELECT clause.


You need to use the HAVING keyword:

SELECT COUNT(ID) AS myCount FROM myTbl
GROUP BY ID
HAVING COUNT(ID) > 1

From MSDN:

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

Tags:

Sql Server

Tsql

Recent Posts