Aggregates of specified size

-- ---------------------------------------------------------------------------
-- Aggregates of specified size
--
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/queries.php
-- ---------------------------------------------------------------------------

-- Find the values of a table column c1 for which there are a specified number
-- of listed values in another column c2.

-- To get an overview of the values of c2 for each value of c1:

SELECT
  c1,
  GROUP_CONCAT(c2 ORDER BY c2) AS 'C2 values'
FROM table
GROUP BY c1;

-- To retrieve a list of c1 values for which there exist specific values in
-- another column c2, you need an IN clause specifying the c2 values and a
-- HAVING clause specifying the required number of different items in
-- the list ...

SELECT c1
FROM table
WHERE c2 IN (1,2,3,4)
GROUP BY c1
HAVING COUNT(DISTINCT c2)=4;

-- This is easy to generalise to multiple column expressions, and a HAVING
-- clause specifying any number of items from the IN list.