CODEWITHSUNDEEP
mysql
Shara
Shara

Reputation: 811

How to collapse this nested MySQL query?

SELECT num, COUNT(num) AS amt
FROM table
WHERE num IN (
    SELECT num
    FROM table
    WHERE id IN (1,2,3))
GROUP BY num
HAVING amt > 1

Also, how can I get

'777' => '2'

.. instead of

'num' => '777'
'amt' => '2'

as result?

thanks

Upvotes: 1

Views: 573

Answers (1)

Johan
Johan

Reputation: 76703

You cannot do a where on a aggregate column, you have to use having for those.

SELECT t1.num, 
       COUNT(t1.num) AS amt 
FROM `table` t1
INNER JOIN `table` t2 ON (t1.num = t2.num)
WHERE t2.id IN (1,2,3)
GROUP BY t1.num
HAVING amt > 1

Upvotes: 3

Related Questions