SQL Method to fill in blank rows of unique identifier

user_15073458 0
Viewed 97

First time poster hoping to get some assistance.

Very minimal coding experience so jargon may be confusing.

I am attempting to use SQL in Zoho to clean up data.

Data consists of A) Transactional Data (premium, fees, net earnings) per policy B) Claims Data (incurred amounts)

Issue is with the unique identifier - same client may have multiple policy numbers with either A) or B) not stored together. What I have been using is the systems own client code (different to policy number) which stores all the policy numbers under the same client. Second issue is Claims data is not mapped out to this 'client code'. Excel index/match/ vlookup have been my go tos in the meantime and has worked fine, however we are moving to Zoho which functions via SQL.

e.g.

| Client Code | Policy Number  | Premium  | Claims |
| --------    | -------------- | -------- | ------ |
| C1          | 123            | 500      | 300    |
| C2          | 456            | 100      |        |
| C1          | 767            | 0        |        | <---
|             | 767            |          | 800    | <--- want these columns put all under C1

Question: How can I Fill in the bottom left blank as C1 using SQL, and then Group each of the clients (C1 & C2) with a Total Premium & Claims amount for them?

GOAL:

| Client Code |  Premium  | Claims |
| --------    | --------- | ------ | 
| C1          |  500      | 1100   |
| C2          |  100      |        |

I've thought of using a Self Join -

SELECT 
t1."Client Code", 
t1."Policy Number", 
t1."Premium", 
t1."Claims", 
t2."Client Code"
FROM table1 as t1
FULL OUTER JOIN 
   (SELECT 
   "Policy Number", 
   "Client Code" 
   FROM table1) t2 
ON t1."Policy Number" = t2."Policy Number"

which clearly does not work, not to mention when I try to include sums by premium, I start receiving group by clause error messages.

Any help would be appreciated.

result:

t1.Client Code t1.Policy Number t1.Premium t1.Claims t2.Client Code
C1 123 500 300 C1
C2 456 100 C2
C1 767 0 C1
C1 767 0
767 800 C1
767 800

Other factors to consider which I've excluded: year of policy, further lines of transaction data due to monthly/ annual payments etc.

zoho ansi-sql sql
1 Answers

If you have a unique identifier, that tells me you want an INNER join. Have a look for venn diagrams of inner vs outer join. An inner join will only you the intersection so no blanks. A left or right outer join will show values in one and possible blanks in the other. You probably don't need full other join. I've rarely come across that.

Regarding aggregating or group. You need GROUP BY and SUM. By grouping by the client code, that field will become unique.

In this case you only have one table of all data so don't need see the need for a join of table1 on itself. Oh I see an issue where multiple claims against the same policy are going to make the policy premium cost appear multiple times and then it would be added up incorrectly.

I am going to leave policy number out of this because your ideal table does use it and want totals across policies not by policy.

Putting that all together.

First, leaving out premium. Don't do too much at once. Build things up.

SELECT 
  "Client Code", 
  SUM("Claims") AS `Total Claims`
FROM table1
GROUP BY "Client Code"

Client code    Total claims
C1                   800
C2                   0

Now just tackling premium. I'm assuming a premium will be fixed for policy number (maybe not?) But that multiple policies for the same client might happen to have the same premium. I don't know how you add up premiums over time...? You'll have to figure that out based on business logic.

SELECT DISTINCT
  "Client Code", 
  "Policy Number",
  "Premium"
FROM table1

The result will be unique rows. Client ID will be repeated but policy number should unique if keep premium value constant.

Then you add aggregation to get the total premiums for a client across policies. But we'll leave that to the end below.

Then you join the two tables together to handle claims and premiums.

SELECT 
    table1."Client Code", 
    SUM(table1.Claims) AS `Total Claims`,
    SUM(Policy.Premium) AS `Total Premiums`
FROM table1
INNER JOIN (
    SELECT DISTINCT
        "Client Code", 
        "Policy Number",
        Premium
     FROM table1
) AS Policy ON Policy."Client Code" = table1."Client Code"
GROUP BY "Client Code"

If you want to look at the underlying data to see joins make sense, then you could remove SUM and SUM and take out the GROUP BY line.

Also your tables and fields are awkwardly named. table1 and t1 and t2 are vague. All your data comes from table1 which is bad for modeling. You rather want a table of clients and their codes and the other tables reference client by row ID like "1" or "789".

And you need field quotes all over the place.

Better structure would be like this. Maybe a table for claims (event based) and policies (contract based).

client.code
policy.client_id

policy.policy_number
policy.premium_value

claim.value
claim.policy_id

Maybe Zoho doesn't let you remodel like this. Hope you can do that.

user_62507330
Related