How to group a subquery for count?

Posted on 2014-08-22
Medium Priority
Last Modified: 2014-09-11
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.
Question by:DrDamnit
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40279292
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
c.uniqueID > p.uniqueid
LVL 32

Author Comment

ID: 40279301
p and c are confusing me.

Please elaborate.
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40279308
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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 14

Expert Comment

by:Russell Fox
ID: 40279309
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 (
		RANK() OVER (PARTITION BY order_number ORDER BY unique_id) AS iRank
	FROM @Orders

FROM OrderRank
WHERE iRank <= quantity_ordered

Open in new window

LVL 32

Author Comment

ID: 40279392
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?
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40279566
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?
LVL 41

Accepted Solution

Kyle Abrahams earned 2000 total points
ID: 40279587
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
c.uniqueID > p.uniqueid

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question