finding multiple records in the same table

I often have a task to get the id column from a table that has multiple ids associated to it - for example in a table of product_master the same product could be bought by mutiple customers - I need to write a sql to find out how many multiple customers have bought the same product.
mahjagAsked:
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.

slightwv (䄆 Netminder) Commented:
select id, count(*) from table group by id having count(*) > 1;

sample data and expected results will help if the above isn't what you are after.
0
Muhammad Ahmad ImranDatabase DeveloperCommented:
select id, cnt from
(select id,count(*) cnt from yourtable group by id )
where cnt > 1
0
mahjagAuthor Commented:
sample data  from product master
for product_id and customer id
first record
product_id = 1_1   customers - c_1
product_id = 1_1   customers - c_2
product_id = 1_1   customers - c_3
product_id = 1_2   customers - c_4

I have to get the product_id that has multiple customers not the record product_id = 1_2 that has only 1 customers

I am learining analytical function and I tried partition by clause

select distinct product_id,customers, count(*) over( partition by product_id order by customers) num_customers where product_id = 1_1
group by product_id,customers

gave me the result as

1_1  c_1 1
1_1  c_2 2
1_1  c_3 3

I dont know how I can evaluate the above reusult to go back to my sql and get only 1_1
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.

mahjagAuthor Commented:
meaning I want to go back to my sql that has partition by and remove where product_id = 1_1 and only want to get records like product 1_1
0
Muhammad Ahmad ImranDatabase DeveloperCommented:
select distinct product_id from
(select distinct product_id,customers, count(*) over( partition by product_id order by customers) num_customers where product_id = 1_1
group by product_id,customers)
0
slightwv (䄆 Netminder) Commented:
count(*) over(...)   returns the overall count on a single row.  probably not what you want here.

Did the other SQL above not work?

This works for me.
drop table tab1 purge;
create table tab1(product_id char(3), customer_id char(3));

insert into tab1 values('1_1','c_1');
insert into tab1 values('1_1','c_2');
insert into tab1 values('1_1','c_3');
insert into tab1 values('1_2','c_4');
commit;

select product_id from tab1 group by product_id having count(*) > 1;

Open in new window

0
mahjagAuthor Commented:
Hi Slightwv

select product_id from tab1 group by product_id having count(*) > 1;  is not working for me if I hardcode where product_id = 1_2 I still got that product - I should not get that product
0
slightwv (䄆 Netminder) Commented:
I don't using my test case above.

Please add to it showing your results.

Here are my results.
SQL> drop table tab1 purge;

Table dropped.

SQL> create table tab1(product_id char(3), customer_id char(3));

Table created.

SQL> 
SQL> insert into tab1 values('1_1','c_1');

1 row created.

SQL> insert into tab1 values('1_1','c_2');

1 row created.

SQL> insert into tab1 values('1_1','c_3');

1 row created.

SQL> insert into tab1 values('1_2','c_4');

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> select product_id from tab1 where product_id='1_2' group by product_id having count(*) > 1;

no rows selected

Open in new window

0
mahjagAuthor Commented:
I agree with you that my actual data does not look like your test data - In my actual data I also have another id - connected_id that is unique on each record and for each customer buying other proudcts I still have unique connected_id that is showing up as multiple records but if I look at the product_id vs customers then it is still 1 record - not sure how I can reflect that in my example -
0
mahjagAuthor Commented:
But if I can go back to my sql

select distinct product_id,customers, count(*) over( partition by product_id order by customers) num_customers where product_id = 1_1
group by product_id,customers

gave me the result as

1_1  c_1 1
1_1  c_2 2
1_1  c_3 3


is there a way to see if the record is more than 1 and the last column has value 1,2,3 I can come up with a sql to say this product id as 3 customers?
0
slightwv (䄆 Netminder) Commented:
>>product id as 3 customers

This is a new requirement.

Why are you wanting to use the window functions?

Using my data example above:
select product_id,count(*) from tab1 group by product_id having count(*) > 1;
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
Oracle Database

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.