Post reply

How to write query for summing duplicate records and insert this summed records into my table after deleting the old duplicate records

  • March 23, 2010 at 5:08 pm

    #74055

    Hi All,

    Can anbody please help me with my doubt, I have been working on this for about weeks..

    I have duplicate data in my table, Table is made of different products of different colors and its quantities in our different - different stores. I want to write query which short the duplicate data of same product type(product_n) with same color(color_code_n) and sum the quantities of similar products of similar colors, then i want to delete the old duplicate records form my table and insert this out put which contains the sum of quantities with similar colors of similar product.

    below given is SELECT statement of my database table products_colors

    SELECT brand_id,product_id,product_color_id,product_n,color_code_n,color_name,image_n,pic1,pic2,pic3,pic4,pic5,offer_clicks,changed_at,most_popular_i,clearance_i,auto_add,product_upc_code,special_order_i,

    QtyLocation1,QtyLocation2,QtyLocation3,QtyLocation4,QtyLocation5,QtyLocation6,QtyLocation7,QtyLocation8,QtyLocation9,QtyLocation10,Username,wholesale_price,my_cost_price,counter_offer_price,temp_status,

    iudate FROM products_colors

    here, in this table there are total 42,000 rows and it has more than 1000 of duplicate entries, this duplicate entries can be grouped by product_n and color_code_n table, identity key column is product_color_id

    Now I want to write a query in such a way that if (COUNT(product_n))>1 AND (COUNT(color_code_n))>1 than it will do SUM(QtyLocation1),SUM(QtyLocation2),SUM(QtyLocation3),SUM(QtyLocation4),SUM(QtyLocation5),SUM(QtyLocation6),SUM(QtyLocation7),SUM(QtyLocation8),SUM(QtyLocation9),SUM(QtyLocation10) and will delete the duplicate entries.

    any suggestion on this please help me out ... will appreciate your response!!

    Thank You,

  • March 23, 2010 at 5:47 pm

    #1138619

    Please help us help you and provide ready to use sample data.

    The most efficient way how to do that is described in the first link in my signature.

    Providing some Excel data might "scare away" some of us who'd be willing to help you if we'd have something to work on without much additional effort...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • March 23, 2010 at 7:24 pm

    #1138644

    try this code and let me know how it works out.

    Fraggle.

    select product_n

    , color_code_n

    , sum(qt1) as qt1

    , sum(qt2) as qt2

    , sum(qt3) as qt3

    , sum(qt4) as qt4

    , sum(qt5) as qt5

    , sum(qt6) as qt6

    , sum(qt7) as qt7

    , sum(qt8) as qt8

    into #temp

    from table

    group by product_n, color_code_n

    having count(*) > 1

    delete from table

    from table as t

    inner join #temp as tmp on t.product_n = tmp.product_n

    and t.color_code_n = tmp.color_code_n

    insert into table (product_n< color_code_n, qt1,qt2....)

    select *

    from #temp

Viewing 3 posts - 1 through 3 (of 3 total)

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