# Duplicate Items Count

Hi,

I am trying to write a query to count duplicate returned items to see how many customers are purchasing a trouser with different size and returning one. e.g

Ordernumber ProductCode Size Descripton Sale Return
1300032 965625617 32 Trouser Short 1 0
1300032 965625617 36 Trouser Long 1 -1
1545335 965992377 42 Trouser Regular 2 0
1576435 965353453 36 Trouser Long 2 -1

Order num: 1300032, this customer purchased 2 trouser with a different size and returned one.

I am trying to produce a result something like below

Total Order Duplicate Returned Order
3 1

How to form a query to achieve the above, any help will be highly appreciated.

Thanks

Regards,
SG

Ordernumber is not the same as customer, and there could be two trousers of different sizes returned for the same ordernumber. But ignoring that, something like this should get you close, although without usable test data it's impossible for me to test it:

``````SELECT COUNT(*) AS [Total Order],
SUM(CASE WHEN Total_Return = 0 THEN 0 ELSE 1 END) AS [Duplicate Returned Order]
FROM (
SELECT Ordernumber, Size, SUM(Sale) AS Total_Sale, SUM(Return) AS Total_Return
FROM dbo.table_name
WHERE Description LIKE 'Trouser%'
GROUP BY Ordernumber
) AS derived``````

I suspect something like the following will get what you want in terms of finding all the orders where a customer ordered multiples of the same thing in different sizes and then returned some of them. There are edge cases that it doesn't cover, such as a customer order 2 size 36 trousers and 2 size 34 and then returning 1 of each, the logic may get considerably more complex if that's important to distinguish but the principle is roughly the same.

``````select
OrderNumber,
ProductCode
from #orders
group by OrderNumber,ProductCode
having
count(size) > 1 -- More than one size of same item
and sum(sale) + sum([return]) > 0 -- Not all were returned
and min([return]) <> 0 -- but at least some were returned
order by OrderNumber,ProductCode
``````

It should be fairly straightforward to integrate that into a full overview report if needed