SQL Select using Oracle

Question

I've been banging my head against my keyboard trying to get this to work. Here's how my simple two column table (named "Test") looks:

part_id cust_id


1..........1

1..........1

2..........2

3..........3

I need to write a select statement that will list all cust_id that ordered the same part_id more than once and also lists the part_id. So far the closest I've been able to come is:

SELECT cust_id, COUNT(part_id)
FROM TEST
GROUP BY cust_id
HAVING COUNT(part_id) > 1;

Which only tells me how many customers ordered more that one part. The same part_id is the key here. Any tips would be greatly appreciated!

Given your sample table, shouldn't you be using cust_id instead of customer_id? Otherwise the query looks fine. — Marc B
– Marc B, Commented Feb 17, 2012 at 14:40

Joe Stefanelli · Accepted Answer · 2012-02-17 14:40:51Z

2

You're so close. Just include the part_id in both the SELECT and GROUP BY.

SELECT cust_id, part_id
FROM TEST
GROUP BY cust_id, part_id
HAVING COUNT(*) > 1;

answered Feb 17, 2012 at 14:40

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Over a year ago

That was it! Thank you so much! This was driving me crazy all afternoon. You just saved my from a sleepless night :)

Collectives™ on Stack Overflow

SQL Select using Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related