sqloracle

SQL - How to Select Entries in a Column that Share Entries in another Column?


I am using a Relational Oracle Database via Toad for Oracle.

I have a table in the Database like this:

LOT_CODE LOT_ID
1000817454 1352341501
1000817455 1312341501
1000817503 1322341601
1000817504 1372341601
1000817535 1352341501

I would like to select LOT_CODES that Share a LOT_ID.

So, in the table above, the answer would be LOT_CODES 1000817454 and 1000817535, since they both Share LOT_ID 1352341501.

But I'd like the Answer to be such that it Counts the instances of LOT_IDs based on how many LOT_CODES have that LOT_ID.

So the Desired Output (based on my sample table above would be):

LOT_ID TOTAL_REPETITIONS
1352341501 2
1312341501 1
1322341601 1
1372341601 1

How do I do this?


Solution

  • Appears you are just asking for a vanilla aggregation

    select LOT_ID, Count(LOT_CODE) TOTAL_REPETITIONS
    from t
    group by LOT_ID;