can't understand why I am getting this result of HAVING COUNT

Question

Here is my sample DB:

CREATE TABLE IF NOT EXISTS `animals` (
  `id` int(6) unsigned NOT NULL,
  `condition` varchar(200) NOT NULL,
  `animal` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `animals` (`id`, `condition`, `animal`) VALUES
  ('1', 'fat', 'cat'),
  ('2', 'slim', 'cat'),
  ('3', 'fat', 'dog'),
  ('4', 'slim', 'dog'),
  ('5', 'normal', 'dog');

I am making the following request:

SELECT result.condition FROM
(
SELECT * FROM animals WHERE animal = 'cat'
UNION
SELECT * FROM animals WHERE animal = 'dog'
) as result

and get what I expect:

condition
---------
fat
slim
fat
slim
normal

Now I want to get only that values which have duplicates. I modify my request by adding the last line:

SELECT result.condition FROM
(
SELECT * FROM animals WHERE animal = 'cat'
UNION
SELECT * FROM animals WHERE animal = 'dog'
) as result
HAVING COUNT(result.condition) > 1

but my actual result is:

condition
---------
fat

While I want to get:

condition
---------
fat
slim

Please tell me what am I doing wrong.

P.S. This part om my request can't be changed.

SELECT * FROM animals WHERE animal = 'cat'
UNION
SELECT * FROM animals WHERE animal = 'dog'

I am simplifying my actual request, but the main idea remains: I get a column of values as a result of UNION of 2 requests.

P.P.S. I am not looking for the most efficient request, I am looking for what will be easier to understand

Answer 1

No need for union. Just filter, aggregate and having:

select `condition`
from animals
where animal in ('cat', 'dog')
group by `condition`
having count(*) > 1

If you do want union, then you need a group by clause in the outer query to make your query a valid aggregation query:

SELECT `condition` 
FROM (
    SELECT * FROM animals WHERE animal = 'cat'
    UNION ALL
    SELECT * FROM animals WHERE animal = 'dog'
) as result
GROUP BY `condition`
HAVING COUNT(*) > 1

Side note: condition is a reserved word in MySQL, so not a good choice for a column name.