SQL Select Count(person_id) > 3 From...

Question

Can someone convert this english to SQL I have tried several things but no luck.

SELECT CASE WHEN COUNT(person_id) > 3 THEN person_id end FROM table

I am trying to only get the person_id(s) that occur > 3 times in the table.

Answer 1

Use:

  SELECT t.person_id
    FROM TABLE t
GROUP BY t.personid
  HAVING COUNT(t.personid) > 3

You can not use aggregate functions, or column aliases to derived columns using aggregate functions, in the WHERE clause. These can only be used in the HAVING clause, which requires defining a GROUP BY clause (if it doesn't already exist).

I don't recommend using the column alias in GROUP BY or HAVING clauses - there's a risk that the query will not be portable to other databases. SQL Server and MySQL are the only databases that I'm aware of that supports column aliases in the GROUP BY or HAVING clauses.

Answer 2

SELECT
    person_id

FROM table

GROUP BY person_id

HAVING COUNT(*) > 3