Select all columns but Group by only some selective column :-

Some times we want to count duplicates over multiples column but output should return all the column from the table.

In this case we can use OVER clause instead of Group by .

Lets have a look below table.

Query : -

create table GroupByExample(

Item int,

ItemDesc varchar(30),

Item_Number int,

ItemDetials varchar(30),

Admin varchar(30)default 'Reza'

)

insert into GroupByExample values(1,'AA',101,'XX',default)

insert into GroupByExample values(2,'BB',102,'YY',default)

insert into GroupByExample values(3,'CC',103,'ZZ',default)

insert into GroupByExample values(1,'AA',101,'XX',default)

insert into GroupByExample values(2,'BB',102,'YY',default)

insert into GroupByExample values(3,'CC',103,'ZZ',default)

insert into GroupByExample values(2,'BB',201,'MM',default)

select * from GroupByExample

Now i want to check duplicates for Item,ItemDesc,Item_Number as well as select all columns from the table.

Execute below Query :

select * ,

count(*) over(partition by Item order by Item) as ItemGroupBy,

count(*) over(partition by ItemDesc order by ItemDesc) as ItemDescGroupBy,

count(*) over(partition by Item_Number order by Item_Number) as Item_NumberGroupBy

from GroupByExample

Output : -