How to select duplicates from one column and unique values from another column in select statement?

Question

I need to run a select statement that returns all rows where the value of column_A is not distinct (e.g. Unv_ID).

For example, if the table looks like below:

Unv_ID           Bus_ID
4000000243771    6000000005041
4000000243771    6000000005089
6000000005132    6000000005042
6000000005132    5000000005173
4000000243771    6000000005041

Ideally I would want to group by Unv_ID (duplicates) and show only unique values (non-duplicates) for Bus_ID.

I am still getting = 1 for Unv_ID - is this because there is only a single entity?

Desired output:

Unv_ID           Bus_ID
4000000243771    6000000005041
4000000243771    6000000005089
6000000005132    6000000005042
6000000005132    5000000005173

Current query is below (I am still getting unique values for both Unv_ID AND Bus_ID)

select Unv_ID, Bus_ID
from "table1" 
where (UNIV_ID in
(select UNIV_ID from "table1" 
group by UNIV_ID having count(*) > 1)
AND
Bus_ID in
(select Bus_ID from "table2" 
group by Bus_ID having count(*) < 1))
order by Bus_ID
;

Answer 1

If you want all unique tuples, you can use distinct:

select distinct univ_id, bus_id from table1 order by univ_id, bus_id

You can print the number of duplicates with aggregation:

select univ_id, bus_id, count(*) as cnt_records
from table1
group by univ_id, bus_id