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:
97138644756 | 3 |
97138644756 | 2 |
97138644756 | 1 |
97138660565 | 5 |
97138660565 | 4 |
97138661777 | 1 |
97138662224 | 1 |
97138670403 | 1 |
97138679239 | 1 |
97138714978 | 1 |
97138716262 | 1 |
97138739667 | 6 |
97138739667 | 7 |
97138739667 | 8 |
I would like it to read:
97138644756 | 6 |
97138660565 | 9 |
97138661777 | 1 |
97138662224 | 1 |
97138670403 | 1 |
97138679239 | 1 |
97138714978 | 1 |
97138716262 | 1 |
97138739667 | 21 |
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.
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
...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>
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
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
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.
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>
Copy link to clipboard
Copied
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