Retrieve duplicates in a column and listing and counting them (SQL on DB2)

Question

I have a table called ServiceRequest. See below some records and columns.

RowID   Type    Area    IntID  OwnerBussUnit
1       AB      DD1     1234   Abc
2       AB      EE2     7635   Abc
3       CD      DD1     1234   Bde
4       FE      FF3     2423   Gte
5       AB      DD1     1234   Abc 
6       CD      DD1     6363   Sde
7       TT      QQ6     7635   Sde
8       AB      DD1     9998   Dfr
9       AB      DD1     9998   Red

---

1) LISTING THEM I would like to list the records that have a duplicate value in the IntID column. Every record in the result should have:

• #IntID: the number of times the duplicate IntID is counted (so can be more than twice)
• #GroupBy: the number of times the duplicate IntID is counted for the combination of the columns 'Type' and 'Area'
• SameOwner; where, in the grouping of Type and Area, OwnerBussUnit has the same value
• DiffOwner; where, in the grouping of Type and Area, OwnerBussUnit does not have the same value Order by IntID, RowID

The result I am looking for is as follows:

IntID  RowID   Type  Area  #IntID  #GroupBy   SameOwner   DiffOwner
1234   1       AB    DD1   3       2           Yes         No
1234   3       CD    DD1   3       1           Yes         No
1234   5       AB    DD1   3       2           Yes         No
7635   2       AB    EE2   2       1           No          Yes
7635   7       TT    OO6   2       1           No          Yes
9998   8       AB    DD1   2       2           No          Yes
9998   9       AB    DD1   2       2           No          Yes

---

2) COUNTING THEM Count duplicate IntID grouped by Type and Area. So result would look like:

Type  Area  #IntID
AB    DD1     4
CD    DD1     1
AB    EE2     1
TT    OO6     1 

How can I do this in SQL (in DB2)?

Solution 1

number 2).

select area, type, count(*) from servicerequest group by area, type

Solution 2

1)

First we need to find which values are duplicated, then of rows with those IntID values, get our grouping by Area & Type, then combine that information with individual rows. Common Table Expressions (CTEs) simplify working things out in stages like this. In this example, i will refer to the first subquery where we find which IntID's have dups, and g the second where we get our info for the groups.

with i as
( select IntId,
         count(*) as tally
    from ServiceRequest
    group by IntID
    having count(*)>1
), g as
( select j.IntId, j.Area, j.Type,
         count(*) as tally,
         count(distinct j.OwnerBussUnit) as owners
    from ServiceRequest j
    join                i   on i.IntID=j.IntID
    group by j.IntId, j.Area, j.Type
)
select x.IntID, x.RowID, x.Type, x.Area,
       i.tally as "#IntID"
       g.tally as "#GroupBy",
       case owners when 1 then 'Yes'
                          else 'No'
        end as SameOwner,
       case owners when 1 then 'No'
                          else 'Yes'
        end as DiffOwner
  from ServiceRequest x
  join                i   on i.IntID = x.IntID
  join                g   on g.IntID = x.IntID
                         and g.Type  = x.Type
                         and g.Area  = x.Area
  Order by x.IntID, x.RowID

2)

Now that we know how to find duplicated values, we can apply this to the the second problem, making it a fairly simple task.

with i as
( select IntId,
         count(*) as tally
    from ServiceRequest
    group by IntID
    having count(*)>1
)
select x.Type, x.Area,
       count(*) as "#IntID"
  from ServiceRequest x
  join                i   on i.IntID = x.IntID
  group by Area, Type
  order by Area, Type

Comments

[WarrenT]

The OP is asking to process only rows that have duplicates in IntID. Your query would erroneously include rows 4 and 6.