CODEWITHSUNDEEP
sqloracle-databaseselectgroup-byduplicates
Imran Hemani
Imran Hemani

Reputation: 629

Duplication of a field based on a column

I have a table SHIPMENT

SHIPMENT       CARTON_ID       ITEM
100             A123           10000243
101             A123           10253456
100             A200           11192423
100             A300           11174523

the carton ID is being repeated for two shipments. I want to know which carton ID is getting repeated for more than one distinct shipment ID i.e A123 getting repeated for 100 and 101

select carton, count (carton) from shipment
where
carton is not null
group by carton, shipment
having count (carton) > 1

what do I need to change in the query?

Upvotes: 1

Views: 27

Answers (2)

Mureinik
Mureinik

Reputation: 311393

You should only group by the carton, and count its distinct shipments:

SELECT   carton, COUNT(DISTINCT shipment)
FROM     shipment
GROUP BY carton
HAVING   COUNT(DISTINCT shipment) > 1

Upvotes: 1

GMB
GMB

Reputation: 222482

You current group by clause generates one row per shipment and carton. Basically, you just need to remove shipment from the group by clause, so you get one group per carton_id: you can then check how many rows belong to the group, as you originally intended:

select carton_id, count(*)
from shipment
where carton_id is not null
having count(*) > 1

If there is a possibility of duplicate (shipment, carton_id) tuples, and you don't want to count them twice, you can use count(distinct shipment) instead of count(*).

Upvotes: 0

Related Questions