selecting when an aggregate column has two or more items

Question

Let's say I had a query like this:

SELECT * FROM (
  SELECT 'a' AS a, '1' AS b
UNION 
  SELECT 'a' AS a, '2' AS b
UNION
  SELECT 'b' AS a, '1' AS b) AS a 
GROUP BY a.a 

In this case "a".b is an aggregate of 1,2 while "b".b is only an aggregate of 1.

How can I select only "a"?

Question updated to be a bit clearer:

Let's take this very similar query:

SELECT *, GROUP_CONCAT(b) FROM (
  SELECT 'a' AS a, '1' AS b
UNION 
  SELECT 'a' AS a, '2' AS b
UNION
  SELECT 'a' AS a, '3' AS b
UNION
  SELECT 'b' AS a, '1' AS b
UNION
  SELECT 'b' AS a, '2' AS b
) AS a 
GROUP BY a.a

Now a.b is 1,2,3 and b.b is 1,2:

I want to select a on the basis that it has 1, 2 and 3.

Answer 1

SELECT  a
FROM    (
        SELECT 'a' AS a, '1' AS b
        UNION 
        SELECT 'a' AS a, '2' AS b
        UNION
        SELECT 'b' AS a, '1' AS b
        ) AS a
GROUP BY
        a
HAVING  COUNT(*) > 1

Note that * syntax that you are using is a MySQL's extension which should not be used like this.

a.b in this case would be not an aggregate: it would be a random record from the group 'a'.

Answer 2

Try:

SELECT a.a,COUNT(*) AS CountOf FROM (
  SELECT 'a' AS a, '1' AS b
UNION 
  SELECT 'a' AS a, '2' AS b
UNION
  SELECT 'b' AS a, '1' AS b) AS a 
GROUP BY a.a
HAVING  COUNT(*) > 1

OUTPUT:

a    CountOf
---- -----------
a    2

(1 row(s) affected)

EDIT based on OP's Comment:

This wouldn't in a situation where a.b was 1,2,3 and b.b was 1,2

try this:

SELECT TOP 1 a.a,COUNT(*) AS CountOf FROM (
  SELECT 'a' AS a, '1' AS b
UNION 
  SELECT 'a' AS a, '2' AS b
UNION 
  SELECT 'a' AS a, '3' AS b
UNION 
  SELECT 'b' AS a, '1' AS b
UNION
  SELECT 'b' AS a, '2' AS b) AS a 
GROUP BY a.a
ORDER BY 2 DESC

OUTPUT:

a    CountOf
---- -----------
a    3

(1 row(s) affected)

Answer 3

The following seems to work for me:

SELECT a,b FROM (
    SELECT a,b FROM (
      SELECT 'a' AS a, '1' AS b
    UNION 
      SELECT 'a' AS a, '2' AS b
    UNION
      SELECT 'a' AS a, '3' AS b
    UNION
      SELECT 'b' AS a, '1' AS b
    UNION
      SELECT 'b' AS a, '2' AS b
    ) AS a 
) b WHERE b.b IN ({x1},{x2}...)
GROUP BY b.a HAVING COUNT(b.b) = 2

Where x1, etc. are the values of "b" that I want to restrict.

For example, in this case

WHERE b.b IN (1,2,3)

will return "a"

WHERE b.b IN (1,2)

will return "a" and "b"

and

WHERE b.b IN (2,3) 

will return only "a"

I'm not sure why this works, but it seems to.