Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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.
0
mahjag
Asked:
mahjag
  • 5
  • 4
  • 2
1 Solution
 
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Featured Post

Industry Leaders: 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!

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now