Search code examples
sqloracle-databaseoracle12c

SQL - Get the values of another column for values in a given column

I am working with a large data table. I want to check the individual items with their destination and the count of destinations if there are more than 1 destination for a given item.

So in the below image,in my result, I should not get Items Grapes and Rice since there is only one destination for them. But I should get the results for Items Apple and Orange.

enter image description here

The query I have written is below. However it does not serve the purpose.

SELECT PrdTbl.Item,,PrdTbl2.cnt
FROM ProductsTable PrdTbl
JOIN
(
SELECT Item,count(*) cnt FROM ProductsTable
GROUP BY Item
HAVING COUNT(*) > 1
) PrdTbl2 ON PrdTbl.Item = PrdTbl2.Item
ORDER BY PrdTbl.Item;

Can someone suggest a solution please? Thanks in advance.

Edit :

Thanks for the original answers.

I think my question needs to be rephrased. I want to filter by the destination as well. While the original condition remains the same, I need to remove the items which are going to the same destination. So here, only Apple and their respective destinations are the ones I need. Orange should be removed because its going to the same destination.

enter image description here

Solution

  • As @forpas said, if you fix the select list then your query does what you said you want:

    SELECT PrdTbl.Item,PrdTbl.Destination,PrdTbl2.cnt
FROM ...

    You could also use an analytic count to avoid the self-join:

    SELECT Item, Destination, cnt
FROM (
  SELECT Item, Destination, count(*) OVER (PARTITION BY Item) as cnt
  FROM ProductsTable
)
WHERE cnt > 1
ORDER BY Item;

ITEM   | DESTINATION | CNT
:----- | :---------- | --:
Apple  | Paris       |   3
Apple  | Rome        |   3
Apple  | London      |   3
Orange | Cape Town   |   3
Orange | New York    |   3
Orange | Cairo       |   3

    db<>fiddle

    I want to filter by the destination as well. While the original condition remains the same, I need to remove the items which are going to the same destination.

    You just need to count distinct destinations, rather than all rows:

    SELECT PrdTbl.Item,PrdTbl.Destination,PrdTbl2.cnt
FROM ProductsTable PrdTbl
JOIN
(
SELECT Item,count(DISTINCT Destination) cnt FROM ProductsTable
GROUP BY Item
HAVING COUNT(DISTINCT Destination) > 1
) PrdTbl2 ON PrdTbl.Item = PrdTbl2.Item
ORDER BY PrdTbl.Item;

    or with analytics:

    SELECT Item, Destination, cnt
FROM (
  SELECT Item, Destination, count(DISTINCT Destination) OVER (PARTITION BY Item) as cnt
  FROM ProductsTable
)
WHERE cnt > 1
ORDER BY Item;

ITEM  | DESTINATION | CNT
:---- | :---------- | --:
Apple | Barcelona   |   5
Apple | London      |   5
Apple | Moscow      |   5
Apple | Paris       |   5
Apple | Rome        |   5

    db<>fiddle