Remove duplicates using SQL query

I recieve duplicate customer bill data which I want to consolidate using SQL query. How to summarize data using single query...both Qty and Price. If either one of price or qty is -ve then both should be -ve.

Here is example data for one customer.

1 – Only Qty is negative

Product Qty Price

Shirt 2 6.00

Shirt -1 6.00

-

Shirt 1 6.00 Result

2 - Only price is negative

Product Qty Price

Pant 2 6.00

Pant 1 -6.00

-

Pant 1 6.00 Result

Thanks for help!!

Pintoo

Based solely upon the limited information and sample data you provided, this solution will meet your requirements.

However, questions arise. Is the price always the same, if not, which price to return? (ABS() may be useful, so could AVG().)

Code Snippet

DECLARE @.MyTable table
( Product varchar(20),
Qty int,
Price decimal(8,2)
)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = max( Price )
FROM @.MyTable
GROUP BY Product

Product Qty Price
-- -- -
Pant 1 6.00
Shirt 1 6.00

|||

Thanks Arnie.

Price is not always same. I have to calculate price by sum(price)/sum(Qty). But how can I use the quantity calculated above to calculate price.

for ex.

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = sum(price*qty)/ sum(qty) caclulated above(how to reuse qty from above query...not sure).

FROM @.MyTable
GROUP BY Product

Thanks

Pintoo

|||

Just the way you expressed it -you were almost there.

Using the ABS() function handles the negative prices and quantities.

Code Snippet

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = ( sum( abs( Price ) * abs( Qty )) / sum( abs( Qty )) )
FROM @.MyTable
GROUP BY Product

|||

How about this query..

DECLARE @.MyTable Table

(

Productvarchar(20),

Qtyint,

Pricedecimal(8,2)

)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

Select

Product

, Sum(Price * Qty/abs(Price))

, Sum(Price * Qty)

From

@.MyTable

Group By

Product

|||

Mani,

With your variation, a negative Price OR Qty adversely effects the ( Price * Qty ) calculation.

Notice what happens when two additional rows are added to the dataset. (Probably not exactly what the OP had in mind...) OnHand quantities might easily be negative or zero, but the average price 'should' reflect the prices used in every transaction and therefore not be negative or zero.

Code Snippet

DECLARE @.MyTable Table
( Product varchar(20),
Qty int,
Price decimal(8,2)
)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, -6.00 )

Select
Product
, Sum( Price * Qty / abs( Price ))
, Sum( Price * Qty )
From @.MyTable
Group By Product

Product
-- - --
Pant -1.00000000000 -6.00
Shirt .00000000000 .00