Using a WHERE clause with count(*)

Question

So I have this query:

SELECT HBODBED, COUNT(*) FROM OCCASIONS WHERE GROEP = 6 GROUP BY HBODBED

It returns this: Except I want to use it in a sub-query so that I could use it in a other query's where clause. To do that I have to return 1 column(HBODBED).

To make matters worse I only want to select the rows where COUNT(*) is 1 to 5.

Does anyone have a solution?

Answer 1

Add a HAVING clause, something like this:

SELECT *
FROM some_other_table
WHERE HBODBED IN (SELECT HBODBED FROM OCCASIONS
                  WHERE GROEP = 6
                  GROUP BY HBODBED
                  HAVING COUNT(*) BETWEEN 1 AND 5);