0

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

Lokraj Belbase
  • 3
  • 4
  • 1
    Does this answer your question? [How to find duplicate records in PostgreSQL](https://stackoverflow.com/questions/28156795/how-to-find-duplicate-records-in-postgresql) – Haleemur Ali Mar 01 '21 at 18:01
  • does this answer your question: https://stackoverflow.com/questions/28156795/how-to-find-duplicate-records-in-postgresql – Haleemur Ali Mar 01 '21 at 18:01

2 Answers2

0

Do you want aggregation with count(distinct)?

select package_no, count(*) as num_rows, count(distinct r1) as num_r1
from t
group by package_no
having count(*) > 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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
Belayer
  • 13,578
  • 2
  • 11
  • 22
  • Thanks for help , second query match my requirement because one road is compulsory available to one package my concern is to count duplicate appearance of road_code to same package . – Lokraj Belbase Mar 02 '21 at 06:50