2

I have a table with duplicate barcodes (barcode) and carts (cart) and (Quantity_counted_IN) and (Quantity_Counted_out).

I want to merge all duplicate records that have the same barcode and cart number to show only one barcode for that cart number but sum(Quantity_counted_IN) and sum(Quantity_Counted_out) at the same time.

Example:

           cart   barcode         Quantity_Counted_In  Quantity_Counted_Out
 Row 1        1   610708542209                     -7                    20
 Row 2        1   610708542209                     -4                    16
 Row 3        2   610708542209                     -3                    17

I want it to read:

 Row 1        1   610708542209                    -11                    36 
 Row 2        2   610708542209                     -3                    17

How can I do this?

0
3

This is what's known as an aggregate query,

SELECT cart, barcode,
       SUM(counted_in) AS counted_in,
       SUM(counted_out) AS counted_out
FROM tbl
GROUP BY cart, barcode;

Using GROUP BY like this returns one record for every unique occurrence of (cart, barcode) and the totals of counted_in and counted_out for each.

0
1

Unless i missed anything maybe(not tested) //WORKS only with SqlServer >=2005

something like this

with CTE as
(
select barcode, sum(counted_in) as counted_in,sum(counted_out) as counted_out
from tbl
group by barcode
)
select (select top 1 cart from tbl where barcode=cte.barcode) as cart, 
barcode,counted_in,counted_out
from cte;
3
  • 1
    Could you explain what you are doing with the correlated subquery? Feb 29 '16 at 6:24
  • I Thought , that even he have a same cart with diferent Barcode , the agregate was by barcode not by cart+barcode. Since the question was so simple your answer is absolutely right :-)
    – Pedro Faro
    Feb 29 '16 at 8:54
  • Please also add that the query will work in SQL Server only (due to the TOP). The OP has not tagged the question with a DBMS. Feb 29 '16 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.