• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Help! Trying to Sum Quantity with Duplicate Rows

Guest
Jan 13, 2012 Jan 13, 2012

Copy link to clipboard

Copied

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.

Views

1.8K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Deleted User
Jan 14, 2012 Jan 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 R

...

Votes

Translate

Translate
Explorer ,
Jan 14, 2012 Jan 14, 2012

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 14, 2012 Jan 14, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jan 14, 2012 Jan 14, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 14, 2012 Jan 14, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jan 14, 2012 Jan 14, 2012

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jan 14, 2012 Jan 14, 2012

Copy link to clipboard

Copied

LATEST

Thanks for posting the resolution.  In case you ever have to do this again, I think you could eliminate the loop.   Just insert the SUM into your temp table.  Then do a join back to the main table to delete the duplicate bar codes.

            INSERT INTO Temp (BarCode, Quantity)

            SELECT barcode, SUM(quantity) as quantitysum

            FROM     cart1

            GROUP BY barcode

            --- exact syntax is db specific

            DELETE FROM c

            FROM   cart1 c INNER JOIN temp t ON t.barcode = c.barcode

Finally, insert the "good" totals back into your main table. Be sure to wrap all three queries in a transaction to preserve data integrity

            INSERT INTO cart1 (BarCode, Quantity)

            SELECT barcode, quantity

            FROM     temp

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation