locked
SQL combining duplicate rows, Please HELP. RRS feed

  • Question

  • User1063681570 posted

    Hello,

    I have a question, what does a statement look like that finds the duplicate rows and combines them,

    I have a table named PRODUCTS in it 3 columbs Cost, Stock, Part_number.

    I need to find all Part_numbers that dublicate, Combine the rows into 1 & combine (sum, add) their stock together is the new row & take an avarerage of their cost and use it as cost in the new row where they combine.

    Please help me, I am stalled. Looked all over the internet & could not find anything, I really need this for a project I can not finish.

    I have the following SQL statement:

    SELECT part_number,

    COUNT(part_number) AS NumOccurrences

    FROM Products

    GROUP BY Part_number

    HAVING COUNT(part_number) > 1

     

    Friday, February 1, 2008 11:42 AM

Answers

  • User1923657670 posted

    Try this (not sure what your field names are): 

    SELECT part_number, AVG(costfield) AverageCost, SUM(stockvaluefield) AS NumOccurrences

    FROM Products

    GROUP BY Part_number

    HAVING COUNT(part_number) > 1  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 1, 2008 1:56 PM

All replies

  • User-1786411686 posted

    Post some sample code from your table and expected output.

    Friday, February 1, 2008 12:13 PM
  • User1063681570 posted

    Hello, thank you for the reply.

    Here is some data:

    2 3CM3C16475CSU 173.42 129
    3 3CM3C16476CSU 323.93 29
    4 3CM3C1670500B 58.59 0

    2 3CM3C16475CSU 159.99 105
    3 3CM3C16476CSU 112.25 919
    4 3CM3C1670500B 60.10 10

    And here is what I want to completed rows to look like:

    So filter and delete duplicates, take avarage of (cost) ,add up (stock)

    2 3CM3C16475CSU 166.70 234
    3 3CM3C16476CSU 218.09 948
    4 3CM3C1670500B 59.34 10

    Friday, February 1, 2008 1:43 PM
  • User1923657670 posted

    Try this (not sure what your field names are): 

    SELECT part_number, AVG(costfield) AverageCost, SUM(stockvaluefield) AS NumOccurrences

    FROM Products

    GROUP BY Part_number

    HAVING COUNT(part_number) > 1  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 1, 2008 1:56 PM
  • User1063681570 posted

    YOUR A GENIOUS !!!!!!!!!!!!!!!!!!!!!!! WORKED LIKE A CHARM. What line should I add to submit that data to a diffrent table OR if possible save the output as new data and delete the old..

    THANK YOU..

    Friday, February 1, 2008 2:19 PM
  • User1613991111 posted

     I have a simular problem hwem I combine two tables i get duplicates but I would like to remove all duplicates leaving the one with the lowest price.

     

     eg

    model      buyprice  

    sua1500i, 190

    sua1500i, 185

    sua3000rmi2u, 400

    sua3000rmi2u,  380

     

    i want to be left with only the lowest price product.

    sua1500i, 185

    sua3000rmi2u, 380













     















    Saturday, August 9, 2008 7:30 AM
  • User-913189835 posted

    I have a similar issue, but in a different view. I'd like to capture data for a report, but the people who built this table made it dynamic. text0-text9, rowindex, and a projectID.

    I dont consider this to be a prudent practice myself, but the code is meant to be dynamic for entry.. just a pain in the rear to report it. :(

    Table: UserInfo

    projID     
    RowIndex
    text0     
    ...text9
    242
      1
    DM@app.net
    Yes
    242
      2
    harry
    123456

    select projID, text0 as Text0A, text0 as Text0B...etc

    Yes I know this isnt the right code. This is liekly to be two select scripts with rowindex=max(rowindex) and a Loop to decrement to 0, then renaming all Text0-9 in Rowindex 1 to be Text#A and so on. I just showed this to sortof give an idea of what i'm shooting for.

    projID  
      Text0A
    text0B  
    etc....
    242  
       DM@app.net harry  
    etc...




    I think I can create 2 select scripts with the same identity number, and then merge/join them as one Query.


    but.. for the life of me.. I cant seem to get this to work. :(


    Friday, November 12, 2010 5:34 PM
  • User1182660020 posted

    here is the sample

    IndianPartyname                ForeignPartyName                  value               qty

    kavita inc                           Boston                                  300                  4

    kavita inc                          Boston                                   400                  3

    suresh indus                      Tanzia                                   200                  6

    Amar indus                        Tanzia                                  700                   3

     

    i want to see the data of indian party against foreignparty with total value & qty by combining duplicates.

     

     

     

    Tuesday, June 21, 2011 10:14 AM