Aggregates of specified size

from the Artful Common Queries page


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.

Return to the Artful Common Queries page