How do I select those records where the group by clause returns 2 or more?

Question

I'd like to return a list of items of only those that have two or more in the group:

select count(item_id) from items group by type_id;

Specifically, I'd like to know the values of item_id when the count(item_id) == 2.

Answer 1

select count(item_id) from items group by type_id having count(item_id)=2;

Answer 2

You're asking for something that's not particularly possible without a subquery.

Basically, you want to list all values in a column while aggregating on that same column. You can't do this. Aggregating on a column makes it impossible to list of all the individual values from that column.

What you can do is find all type_id values which have an item_id count equal to 2, then select all item_ids from records matching those type_id values:

SELECT item_id
FROM items
WHERE type_id IN (
  SELECT type_id
  FROM items
  GROUP BY type_id
  HAVING COUNT(item_id) = 2
)

This is best expressed using a join rather than a WHERE IN clause, but the idea is the same no matter how you approach it. You may also want to select distinct item_ids in which case you'll need the DISTINCT keyword before item_id in the outer query.

Answer 3

If your SQL dialect includes GROUP_CONCAT(), that could be used to generate a list of items without the inner query. However, the results differ; the inner query returns one item id per row, where GROUP_CONCAT() returns multiple ids as a string.

SELECT type_id, GROUP_CONCAT(item_id), COUNT(item_id) as number
FROM items
GROUP BY type_id
HAVING number = 2