CODEWITHSUNDEEP
sqloracle-databasenormalization
Sun
Sun

Reputation: 2715

Oracle: Combining similar names together

ItemNo  Name      Requested Qty
850045  MICHAEL         46  1045
850045  MICHAEL JACKSON 38  834
850045  LARRY SHEARIN   22  473
850045  Michael Jackson 11  233
850045  Larry           5   84

I have a table where Requester Name is not normalized. Michael and Michael Jack are the same person. Larry and Larry Shearin are also the same person. Is there a way to combine row data so that the Requested and Qty also sum up correctly? I was thinking there might be some sort of Oracle function or analytic that would do this...

ItemNo  Name      Requested Qty
850045  MICHAEL JACKSON 95  2112
850045  LARRY SHEARIN   27  557

Upvotes: 0

Views: 98

Answers (1)

sgeddes
sgeddes

Reputation: 62831

There may be another way, but this should work using UPPER and matching any firstname (without spaces) to any fullname (with a space) -- if multiple full names match, you're results will be inaccurate.

SELECT T.ItemNo, 
  T.Name, 
  T.Requested + T2.Requested Requested,
  T.Qty + T2.Qty Qty
FROM (
    SELECT ItemNo, UPPER(Name) as Name, SUM(Requested) Requested, SUM(Qty) Qty
    FROM YourTable
    WHERE Name LIKE '% %'
    GROUP BY ItemNo, UPPER(Name)
  ) T
  JOIN (
    SELECT ItemNo, UPPER(Name) as Name, SUM(Requested) Requested, SUM(Qty) Qty
    FROM YourTable
    WHERE Name NOT LIKE '% %'
    GROUP BY ItemNo, UPPER(Name)
  ) T2 ON T.ItemNo = T2.ItemNo AND T.Name LIKE T2.Name||' %'

Here is the SQL Fiddle.

And here are the results:

ITEMNO  NAME            REQUESTED  QTY
850045  MICHAEL JACKSON 95         2112
850045  LARRY SHEARIN   27         557

I assume you're total (32) for Larry was mistaken above (22 + 5)?

Hope this helps.

Upvotes: 1

Related Questions