Need to return all columns from a table when using GROUP BY

Question

I have a table let's say it has four columns Id, Name, Cell_no, Cat_id. I need to return all columns whose count of Cat_id is greater than 1.

The group should be done on Cell_no and Name. What i have done so far..

select Cell_no, COUNT(Cat_id)
from TableName
group by Cell_Number
having COUNT(Cat_id) > 1

But what i need is some thing like this.

select * 
from TableName
group by Cell_Number
having COUNT(Cat_id) > 1

Answer 1

Pratik's answer is good but rather than using the IN operator (which only works for single values) you will need to JOIN back to the result set like this

SELECT t.* 
FROM tableName t
INNER JOIN      
    (SELECT Cell_no, Name 
    FROM TableName
    GROUP BY Cell_no , Name
    HAVING COUNT(Cat_id) > 1) filter
    ON t.Cell_no = filter.Cell_no AND t.Name = filter.Name

Answer 2

you just need to modify your query like below --

select * from tableName where (Cell_no, Name) in (
          select Cell_no, Name from TableName
           Group by Cell_no , Name
           having COUNT(Cat_id) > 1
           )

as asked in question you want to group by Cell_no and Name.. if so you need to change your query for group by columns and select part also.. as I have mentioned

Answer 3

This version requires only one pass over the data:

SELECT *
FROM   (SELECT a.*
              ,COUNT(cat_id) OVER (PARTITION BY cell_no)
               AS count_cat_id_not_null
        FROM   TableName a)
WHERE  count_cat_id_not_null > 1;