Is there any query to find duplicate records count corresponding to package_no column?

I have a table structure like this

package_no   road_name road_code
p1            r1         c1
p1            r1         c1
p2            r1         c1

I Need to count duplicate road code corresponding to the column package_no I expect one record from the above table with a duplicate package p1 and road count with 1

Answer

You have a consistency problem. In your initial description you say “duplicate road code corresponding to the column package_no”, however in response to @GordonLinoff yoe indicate you want “duplicate appearance of r1”. Yet “r1” corresponds to road_name. So which is it: road_Code or rode_name. Either way:

select t.package_no, t.road_code, count(*) - 1 "# Duplicates" 
  from test t
 group by t.package_no, t.road_code
 having count(*) > 1;

Just replace road_name by road_code is required.
The query returns count – 1 as per initial requirement “p1 and road count with 1”

But what would the result be if the following also existed:

package_no   road_name road_code
p1            r1         c2
p1            r1         c2
p1            r2         c1
p1            r2         c1
p1            r2         c2