Tutorial :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.


Solution: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.


Solution:2

SELECT      person_id    FROM table    GROUP BY person_id    HAVING COUNT(*) > 3  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »