CODEWITHSUNDEEP
sqloracle-database
born2bmild
born2bmild

Reputation: 144

Oracle. Not able to return multiple Columns with Count

I'm having trouble with a select statement. I am getting the data of 3 columns from a products table(view) and everything is fine:

Select bin_number, part_number, qty
From Products V
Where Site = 'My_Site';

This gives me 5000+ rows.
The Problem is: The same part_number is in several different bins.

I have no problem getting the count on part_number:

Select part_number, COUNT(*)
FROM Products V
Where Site = 'My_Site'
Group By part_number
Having COUNT(*) > 1;

This returns 600 + rows of duplicate part_number(s) and how often they are duplicated, but I also need the bin_number and Qty columns. Everything I've tried using the other two columns gives me an empty result set.

Any help would be greatly appreciated. Thanks

Upvotes: 0

Views: 113

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

This will return parts that are in more than one bin.

Select part_number, COUNT(distinct bin_number)
FROM Products V
Where Site = 'My_Site'
Group By part_number
Having COUNT(distinct bin_number) > 1;

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

Try using Count Over()

select * from 
(
Select bin_number, part_number, qty,count(1) over(partition by part_number) as cnt
From Products V
Where Site = 'My_Site'
)
where cnt > 1;

Upvotes: 1

Related Questions