Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Merge and Sum Similar Records

    Hi,

    I have the below in a table - TABLEA

    Ref, Date, TIME, Code, Minutes
    01117, 2012-01-02, 541, BASIC, 240.0
    01117, 2012-01-02, 541, BASIC, 105.0

    And I am trying to insert this into another table TABLEB but it wont allow as I am getting a duplicates error because of the unique Indexing on the table.

    Ref,Date,Time,Code

    Ideally if I could run a query on TABLEA so that it would merge and sum the minutes where REF,Date,TIME,CODE are the same.

    ie the above would become

    Ref , Date , TIME, Code, Minutes
    01117, 2012-01-02, 541, BASIC, 345.0

    Is this possible?

    Another option that would work for me is the TIME column info isnt required to remain at 541.

    If there was a count increment on the rows it would allow the import to rum.

    ie if the above became

    Ref, Date, TIME, Code, Minutes
    01117, 2012-01-02, 1, BASIC, 240.0
    01117, 2012-01-02 , 2 BASIC, 105.0

    It would also import correctly.

    The first option is which I would prefer.

    Any help appreciated.

    Regards

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is a simple aggregate function. Look up how to use them on Books Online.
    Code:
    insert into TableB
    	(Ref,
    	Date,
    	Time,
    	Minutes)
    select	Ref,
    	Date,
    	Time,
    	sum(Minutes)
    from	TableA
    group by Ref,
    	Date,
    	Time
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2011
    Posts
    44
    Perfect - Thanks

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •