CODEWITHSUNDEEP
sql-servert-sql
Shah
Shah

Reputation: 1654

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

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

Upvotes: 6

Views: 6346

Answers (3)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28728

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

Upvotes: 3

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

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;

Upvotes: 1

pratik garg
pratik garg

Reputation: 3342

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

Upvotes: 1

Related Questions