How to group a subquery for count?

I have rows of orders. Each order has multiple items. But, there are some orders that have been duplicated, and I need tofind them.

Assuming that my table has the following columns:
unique_id (int)
order_number (int)
item (varchar(100)).
quantity_ordered (int) - This is the total quantity for teh entire order.


SO, an example order would be:
1 - 1234 - Some Item - 3
2 - 1234 - Some other item - 3
3 - 1234 - some other item - 3

SO, here, we can see that this or has three items (last column) and that there are, indeed, 3 and only 3 in the order table.

But, here's the problem. Some were submitted twice. So we have:
1 - 1234 - Some Item - 3
2 - 1234 - Some other item - 3
3 - 1234 - some other item - 3
37 - 1234 - Some Item - 3
38 - 1234 - Some other item - 3
39 - 1234 - some other item - 3

So, even thought the order quantity is 3, there are 6 rows with this order number.

I need to write a query that finds all the orders where the number of rows for that order is greater than the ordered quantity (the last column in my example).

I am pretty sure a subquery is in order, but I cannot figure out the logic.
LVL 32
DrDamnitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
You could do this with a self join if you're just looking for duplicates:


select p.uniqueid, c.* from table p
join table c on p.order_number  = c.order_number and p.item = c.item --can also add quantity
where
c.uniqueID > p.uniqueid
0
DrDamnitAuthor Commented:
p and c are confusing me.

Please elaborate.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
it's an alias.

You only have one table.

I'm calling the first instance  "P" (parent)
the second instance (even though it's the same  table) "C"  (child).

I'm then joining the instances based on the order number and the item.  
then I'm filtering where the child's unique ID > then the parent unique id.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Russell FoxDatabase DeveloperCommented:
You can use the RANK() function along with the quantity field:
--	Set up sample data -----------------------------------------------
DECLARE @Orders TABLE (unique_id INT, order_number INT, item VARCHAR(100), quantity_ordered INT)

INSERT INTO @Orders ( unique_id ,   order_number ,  item ,  quantity_ordered )
VALUES ( 1 , 1234, 'Item 1', 3),
	( 2 , 1234, 'Item 2', 3),
	( 3 , 1234, 'Item 3', 3),
	( 45 , 1234, 'Item 1', 3),
	( 46 , 1234, 'Item 2', 3),
	( 47 , 1234, 'Item 3', 3)
--	------------------------------------------------------------------
; WITH OrderRank
AS (
	SELECT *,
		RANK() OVER (PARTITION BY order_number ORDER BY unique_id) AS iRank
	FROM @Orders
)

SELECT *
FROM OrderRank
WHERE iRank <= quantity_ordered

Open in new window

0
DrDamnitAuthor Commented:
Regarding p and c, my table name is tbl_orders.

How do I alias that to p and c? I am not seeing that in the query above?
0
Scott PletcherSenior DBACommented:
Can the same item appear multiple times for the same order_number, in different locations?  Or would the same item quantities always be combined?

1 - 1234 - ItemA - 3
2 - 1234 - ItemB - 3 --I changed the item to see a difference: are ids 2 and 3 actually supposed to be different items??
3 - 1234 - ItemC - 3
4 - 1234 - ItemB - 3 --is this valid?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
select p.uniqueid, c.* from tbl_orders p
join tbl_orders c on p.order_number  = c.order_number and p.item = c.item --can also add quantity
where
c.uniqueID > p.uniqueid
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.