MySQL: how to get records with count > 1?

I have a sql like this:

SELECT *, count(*) as cc 
FROM manytomany 
GROUP BY aid, bid
ORDER BY cc DESC

which return all records with the count #.

however, what can I do if I only want to get the ones with count > 1开发者_如何学编程?

SELECT *, count(*) as cc 
FROM manytomany 
GROUP BY aid, bid
HAVING 1 < count(*)
ORDER BY cc DESC

You need a HAVING clause, for example:

SELECT *, count(*) as cc 
FROM manytomany 
GROUP BY aid, bid
HAVING COUNT(*) > 1
ORDER BY cc DESC

Here's some background.

You use the having clause.

SELECT *, count(*) as cc
FROM manytomany
GROUP BY aid, bid
HAVING count(*) > 1
ORDER BY cc DESC

SELECT *, count(*) as cc 
FROM manytomany 
GROUP BY aid, bid
HAVING COUNT(*) > 1
ORDER BY cc DESC

I don't use MySQL, but it should support HAVING -it has been around for along time.

SELECT *, count(*) as cc 
FROM manytomany 
GROUP BY aid, bid
HAVING cc>1
ORDER BY cc DESC

The HAVING clause

   SELECT *, count(*) as cc 
    FROM manytomany 
    GROUP BY aid, bid
    HAVING cc > 1
    ORDER BY cc DESC