Post reply

Group by question

  • 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

    1 Amy Paul 2 Main St, Acton,MA 2013/03/02

    1 Amy Claire Paul 2 Main St, Acton,MA 2014/01/09

    2 Roger 5 North Tenth St, Emmaus PA 2009/02/12

    3 Lesley A 234 Waltham St, Salem PA 2009/05/05

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

    I want to print all info of records that have count(*) > 1 but group by ID only.

  • March 27, 2014 at 8:01 am

    #1700691

    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

  • March 27, 2014 at 8:05 am

    #1700696

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

  • March 27, 2014 at 8:06 am

    #1700698

    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/

  • March 27, 2014 at 8:14 am

    #1700703

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

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

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