CODEWITHSUNDEEP
sqldatabase
Tanishq Chaudhary
Tanishq Chaudhary

Reputation: 54

How to run this query?

I have the following table

MID IID

1   23

1   24

1   25

2   32

2   54

3   11

4   55

5   67

and i want to find all the MID's where count(IID) >1 i.e Result should be following

MID count(IID)

1   3

2   2

P.S - I tried but i am getting result for all the MID's . Here is my query

select distinct a.MID,count(a.IID) from Item_attribute as a group by a.MID

Upvotes: 1

Views: 40

Answers (4)

toonice
toonice

Reputation: 2236

Please try the following...

SELECT MID,
       COUNT( IID ) as CountIID
    FROM Item_attribute
    GROUP BY MID
HAVING COUNT( IID ) > 1;

For your other query please try the following...

SELECT MID,
       CountIID
FROM
(
    SELECT MID,
           COUNT( IID ) as CountIID
    FROM Item_attribute
    GROUP BY MID
    HAVING COUNT( IID ) > 1
) AS limitedCountIIDFinder
WHERE CountIID =  ( SELECT MAX( CountIID ) AS MaxCountIID
                    FROM
                    (
                        SELECT MID,
                               COUNT( IID ) as CountIID
                        FROM Item_attribute
                        GROUP BY MID
                    ) AS countIIDFinder
                  )

(Explanation to follow...)

If you have any questions or comments, then please feel free to post a Comment accordingly.

Upvotes: 0

Ofir Winegarten
Ofir Winegarten

Reputation: 9355

When you'd like to have conditions on an aggregated field, you need to use having.
You also don't need to use distinct if you are using group by.
In your case:

select a.MID,count(a.IID) from Item_attribute as a group by a.MID having count(a.IID)>1

If you want to get the max count value then sort it descending and get top 1:

select top 1 a.MID,count(a.IID) 
from Item_attribute as a 
group by a.MID 
having count(a.IID)>1 
order by count(a.IID) desc

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

Use HAVING clause in SELECT statement :

SELECT a.MID,count(a.IID) 
FROM Item_attribute as a 
GROUP BY a.MID
HAVING COUNT(a.IID) > 1

Upvotes: 0

dev8080
dev8080

Reputation: 4020

Add having clause like this: 

having count(a.IID)>1

Upvotes: 1

Related Questions