Duplicate Items Count


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.



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]
    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.

from #orders
group by OrderNumber,ProductCode
	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