CODEWITHSUNDEEP
mysql
collin ter steege
collin ter steege

Reputation: 89

Using a WHERE clause with count(*)

So I have this query:

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

It returns this: enter image description here 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?

Upvotes: 0

Views: 750

Answers (2)

derpirscher
derpirscher

Reputation: 17382

If you don't want the COUNT to be returned, just don't include it in the SELECT. For conditions over the aggregation function, you can use the HAVING clause.

SELECT hbodbed 
FROM occasions 
WHERE grouep = 6 
GROUP BY hbodbed 
HAVING COUNT(*) BETWEEN 1 AND 5

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

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);

Upvotes: 4

Related Questions