views:

181

answers:

6

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?

+7  A: 
SELECT *, count(*) as cc 
FROM manytomany 
GROUP BY aid, bid
HAVING 1 < count(*)
ORDER BY cc DESC
just somebody
I've never written it as `HAVING 1 < count(*)`, but it works.
OMG Ponies
Why would you put the 1 on the left hand side of the expression? Sure, it's logically consistent, but just curious...
gahooa
readability. it's easier to quickly grasp the structure of the expression if the infix operator is closer to the start of the expression: `1 < bla-bla-bla-bla...` rather than `bla-bla-bla-bla > 1`
just somebody
+1  A: 

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.

martin clayton
+1  A: 
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.

cdonner
A: 

The HAVING clause

   SELECT *, count(*) as cc 
    FROM manytomany 
    GROUP BY aid, bid
    HAVING cc > 1
    ORDER BY cc DESC
mabwi
+2  A: 

You use the having clause.

SELECT *, count(*) as cc
FROM manytomany
GROUP BY aid, bid
HAVING count(*) > 1
ORDER BY cc DESC
Jason Punyon
+1  A: 
SELECT *, count(*) as cc 
FROM manytomany 
GROUP BY aid, bid
HAVING cc>1
ORDER BY cc DESC
Blindy