Group by question

Question

Post reply

Group by question

rash3554

SSCommitted

Points: 1984
More actions
March 27, 2014 at 7:54 am

#305655

I have a table that has these columns
ID, Name, Address,DateInserted. ID and Name are primary keys.
Date is as follows
ID Name Address DateInserted
1Amy Paul2 Main St, Acton,MA 2013/03/02
1Amy Claire Paul2 Main St, Acton,MA 2014/01/09
2Roger5 North Tenth St, Emmaus PA 2009/02/12
3Lesley A234 Waltham St, Salem PA 2009/05/05
3Lesley Abdel94 Cherry St, Salem PA 2010/09/03
I want to print all info of records that have count(*) > 1 but group by ID only.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

SELECT ID, Cnt = COUNT(*)

FROM MyTable

GROUP BY ID

HAVING COUNT(*) > 1;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rash3554 SSCommitted Points: 1984 More actions · Answer 2

I want to display name,address,date inserted but group by only ID.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Here is one way.

create table #Something

(

ID int,

Name varchar(50),

Address varchar(50),

DateInserted datetime

)

insert #Something

select 1, 'Amy Paul', '2 Main St, Acton,MA', '2013/03/02' union all

select 1, 'Amy Claire Paul', '2 Main St, Acton,MA', '2014/01/09' union all

select 2, 'Roger', '5 North Tenth St, Emmaus PA', '2009/02/12' union all

select 3, 'Lesley A', '234 Waltham St, Salem PA', '2009/05/05' union all

select 3, 'Lesley Abdel', '94 Cherry St, Salem PA', '2010/09/03'

select *

from #Something

where ID in

(

select ID

from #Something

group by ID

having COUNT(*) > 1

)

drop table #Something

Notice how I posted ddl and sample data in a consumable format. That is something you should do in the future.

Also, you should avoid reserved words. Last but not least, you should NOT store more than 1 piece of information in a single column. An address is not 1 column, it is several columns. Same with names, you should have at least a first and last name column.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rash3554 SSCommitted Points: 1984 More actions · Answer 4

Thanks for the link to post the code properly & for the answer too.