Group by Issue

Question

Post reply

Group by Issue

DBTeam

SSC Eights!

Points: 956
More actions
September 14, 2010 at 12:14 am

#240274

Hi All,
I had some problem with getting output.
My criteria is I want to select records which are having
duplicates in name and company name.
Below is the sample data that I have pasted here.
I have a very large table where I need to pull all the records which are having different Emails but with the same Name and company.
CompanyName Email ID
TCS Suneethasuneetha@tcs.com 1
TCS Suneethasunitha@tcs.com 2
TCS soumyasoumya@tcs.com 3
TCS soumyasoumya.raja@tcs.com 4
wipro nitha nitha@wipro.com 5
infosys Githa Githa@infosys.com 6
SMS vani vani@sms.com 7
SMS uma uma@sms.com 8
capgemini vani vani.D@capgemini.com 9
Microsoft Uma Uma.fam@microsoft.com 10
I am getting Count easily with the below query.
select name,company,COUNT(*) from tab
group by name,Company having COUNT(*)>1
Problem is I need all the data with all columns for the above counts
Ex: I need output like this..
CompanyName Email ID
TCS Suneethasuneetha@tcs.com 1
TCS Suneethasunitha@tcs.com 2
TCS soumyasoumya@tcs.com 3
TCS soumyasoumya.raja@tcs.com 4
Please help me out in this as I am not at all getting this.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Try something like this:

with preptab as (

select name,company,Email,row_number() over (partition by company,name order by email) as RowCnt

from tab

)

Select Name, Company, Email,RowCnt

From preptab

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

I think i know how to do that, but i need clear "expected output"...Where do u what the count column to fit it? Can u please be more specific with your expected result?

DBTeam SSC Eights! Points: 956 More actions · Answer 3

I do not want any counts i need all the columns and its data like the below format

Ex: I need output like this..

Company Name Email ID

TCS Suneethasuneetha@tcs.com 1

TCS Suneethasunitha@tcs.com 2

TCS soumyasoumya@tcs.com 3

TCS soumyasoumya.raja@tcs.com 4

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 4

Hi,

Here is the table format.

create table #temp(company varchar(50),name varchar(50),email varchar(50),Id int)

insert into #temp

select 'TCS', 'Suneetha','suneetha@tcs.com', 1

union all

select 'TCS', 'Suneetha', 'sunitha@tcs.com', 2

union all

select 'TCS', 'soumya', 'soumya@tcs.com',3

union all

select 'TCS', 'soumya' ,'soumya.raja@tcs.com',4

union all

select 'wipro', 'nitha', 'nitha@wipro.com', 5

union all

select 'infosys', 'Githa', 'Githa@infosys.com', 6

union all

select 'SMS', 'vani', 'vani@sms.com', 7

union all

select 'SMS', 'uma', 'uma@sms.com', 8

union all

select 'capgemini', 'vani' ,'vani.D@capgemini.com',9

union all

select 'Microsoft', 'Uma', 'Uma.fam@microsoft.com',10

Your expected result.

select T1.company,T1.name,T1.email,T1.id

from #temp T1

where exists(select T2.company,T2.name from #temp T2

where T1.company=T2.company and T1.name=T2.name

group by T2.company,T2.name

having count(*)>1)

the above query will give you the result you want please check it.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Here's my dig :

(using the table set-up by malleswar_reddy)

;with cte as (

select name,company,Email ,Id,RowCnt = COUNT(*) over (partition by company,name)

from #temp

)

Select Name, Company, Email,Id

From cte

WHERE RowCnt > 1

ORDER BY Id

DBTeam SSC Eights! Points: 956 More actions · Answer 6

Thank You Soooooooooooooooooo much. I got it with Partition

I got the result in less than a min

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

ColdCoffee

SSC-Dedicated

Points: 39972

September 14, 2010 at 2:58 am

#1220528

Thanks for the feedback, DBTeam... 😀

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Good to know you got it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events