Skip to main content
A
Anonymous
January 14, 2012
Answered

Help! Trying to Sum Quantity with Duplicate Rows

  • January 14, 2012
  • 2 replies
  • 2042 views

I have a database that have the following columns barcode, quantity, etc. The barcode colum has barcode duplicates and the corresponding quantity differs. I have been working hours trying to solve how to sum each different barcode and replace the table with 1 barcode each with corresponding total quantity. So, instead of:

971386447563
971386447562
971386447561
971386605655
971386605654
971386617771
971386622241
971386704031
971386792391
971387149781
971387162621
971387396676
971387396677
971387396678

I would like it to read:

971386447566
971386605659
971386617771
971386622241
971386704031
971386792391
971387149781
971387162621
9713873966721

I was using the following code, which only picks out the duplicates.

<cfquery name="CountBarcodesCart1" datasource='inventory'>

SELECT *

FROM cart1

WHERE barcode IN (

SELECT barcode

FROM cart1

GROUP BY barcode 

HAVING (COUNT(barcode ) > 1))

</cfquery>

Thanks for anyone that can help with my business.

    This topic has been closed for replies.
    Correct answer

    I know there is a lot better way to do this, but I will share the following code and hope it will help someone else. What I did was change from CART1 table to a TEMP table, get the dupliates added up and then output the corrected data (with no duplicates) to a new table. Then, I delete the TEMP table.

    <!--- FIND DUPLICATES AND SUM QUANTITY --->
    <cfquery name="CountBarcodesTemp" datasource='inventory'>
    SELECT barcode, sum(quantity) as quantitysum
    FROM temp 
    GROUP BY barcode
    </cfquery>

    <!--- OUTPUT RESULTS AND INSERT INTO NEW TABLE--->
    <cfoutput query="CountBarcodesTemp">
            <cfquery name="InsertIntoCart" datasource="inventory">
      INSERT INTO Cart1 (barcode, quantity)
      VALUES (#CountBarcodesTemp.barcode#, #CountBarcodesTemp.quantitysum#)
      </cfquery>
    #barcode# -- #QuantitySum#<br /><br/>
    </cfoutput>

    <!---         DELETE THE TEMP TABLE  --->
    <cfquery name="DeleteBarcodesTemp" datasource='inventory'>
    DELETE *
    FROM Temp
    </cfquery>

    

    2 replies

    R
    RLByrd78240
    Inspiring
    January 14, 2012

    If I'm understanding what your desired output is, wouldn't adding a "distinct" qualifier in your SELECT statement do that?  (SELECT distinct barcode, qty) I'm learning too

    A
    Anonymous
    January 14, 2012

    From what I understand, Distinct would output 1 barcode each, but would not sum the quantity of each barcode. I am actually trying to come up with code to modify the database to remove duplicates and quantity OR transfer the corrected output to a new table so that it only has 1 bar code each. This is kind of like if you were selling different concert tickets and you wanted to sum the sale of each kind of ticket. - Thanks though.

    A
    Anonymous
    Correct answer
    January 14, 2012

    I know there is a lot better way to do this, but I will share the following code and hope it will help someone else. What I did was change from CART1 table to a TEMP table, get the dupliates added up and then output the corrected data (with no duplicates) to a new table. Then, I delete the TEMP table.

    <!--- FIND DUPLICATES AND SUM QUANTITY --->
    <cfquery name="CountBarcodesTemp" datasource='inventory'>
    SELECT barcode, sum(quantity) as quantitysum
    FROM temp 
    GROUP BY barcode
    </cfquery>

    <!--- OUTPUT RESULTS AND INSERT INTO NEW TABLE--->
    <cfoutput query="CountBarcodesTemp">
            <cfquery name="InsertIntoCart" datasource="inventory">
      INSERT INTO Cart1 (barcode, quantity)
      VALUES (#CountBarcodesTemp.barcode#, #CountBarcodesTemp.quantitysum#)
      </cfquery>
    #barcode# -- #QuantitySum#<br /><br/>
    </cfoutput>

    <!---         DELETE THE TEMP TABLE  --->
    <cfquery name="DeleteBarcodesTemp" datasource='inventory'>
    DELETE *
    FROM Temp
    </cfquery>

    
    Tayyab Hussain
    Tayyab Hussain
    Inspiring
    January 14, 2012

    I assumed 97138644756 is Barcode 3,2,1 are quantities

    <cfquery name="CountBarcodesCart1" datasource='inventory'>

    SELECT barcode, sum(qty) as "qty"

    FROM cart  GROUP BY barcode

    </cfquery>

    
    A
    Anonymous
    January 14, 2012

    Thanks, but that doesn't help deleting my duplicates and updating the total quantity of each barcode to the correct barcode. Instead of for example, 3 rows of the same barcode number, I want just 1 row with the total sum of all quantity of that barcode. As with my above code I first posted, I can find my duplicates, but it is tricky to sum the total quanity of each barcode instead of the whole column of quantity.

    After removing some of my code, You code does work great to output the desired results. I just now have to figure out how to remove the duplicates or just send the output data to a new table. - Thanks.

    Message was edited by: geraldselectric225

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices