How to Get the count of Same ID

[niladri.prima]

Hi All,

I have 44,000 data in a table (dbo.IA_Subscription) where I have only 2 columns; one is ID and another is Magazine_Type

ID filed is filled with contact_ID and Magazine_Type is filled with 3 Magazine e.g. Papaer Type, Digital Type and Web Type.

Now contact_ID is not unique...one contact_ID can have 3 magazine or 2 magazine. I want to get the result of the sames ID who have bought 2 or 3 magazines and the magazine name also.

Is there any way to do that? I have tried Pivot table option in Excel and SQL also. But I am not getting the exact result.

Remember one thing that ID is case sensitive like a0Q700000033Gww and a0Q700000033GwW. But these 2 are different Ids.

Can anyone help me?

Regards,

Niladri Sekhar Biswas

[jalpesh_007]

write query using group by keyword.

[adam_k]

select contact_ID, Magazine_Type from dbo.IA_Subscription a 
inner join 
(select contact_ID, count(*) as magazines from dbo.IA_Subscription 
group by contact_ID
having count(*) > 1 ) b 
on a.contact_ID = b.contact_ID