Sum values from 2 joined tables even if id’s don’t match

I’m trying to take two tables, join them together based on their contract numbers and then get a sum of each customers contract amount between two years.

However, some of these contracts were from an older system in which we do not have record of them in the current system. What we do have is an archived table that shows what the price of the contract was, but no ties to the old contract. The reason I can’t just take it from the archived table is that even after these contracts get archived, they can be deleted or cancelled anytime after that.

So, I’m trying to figure out how I can join both of these tables and get the sum of the customers contract totals when some are tied to a contract number and some are not?

I know this is hard to explain but hopefully by showing the schema and query it will help.

This is our archives table schema which holds an archive of every single contract a customer has had from both the old and new system.

|customer_id | contract_amount | date       | contract_number |
|1           | 300.00          | 2019-02-01 | abc123          |
|1           | 250.00          | 2019-02-03 | abc456          |
|1           | 320.00          | 2019-04-02 | abc789          |
|1           | 230.00          | 2019-07-03 | efg123          |

Contract table schema:

|customer_id | contract_amount | date_authorized | contract_number | status   |
|1           | 300.00          | 2019-01-03      | abc123          | Approved |
|1           | 250.00          | 2019-01-06      | abc456          | Cancelled|
|1           | 320.00          | 2019-02-01      | abc789          | Approved |

Here is the query I’m currently running to get customer 1’s contract total between 2020 and 2019:

select a.customer_id
     , sum(a.contract_amount) as year_total 
  from Archives_Table a 
  left 
  join Contracts_Table c 
    on (a.contract_number = c.contract_number) 
 where a.date >= '2019-01-01' 
   and a.date < '2020-01-01' 
   and c.status = 'Approved' 
   and a.customer_id = '1';

And the result I get is:

|customer_id | year_total |
|1           | 620.00     |

But I should be getting this:

|customer_id | year_total |
|1           | 850.00     |

The contract number efg123 comes from the old system which we do not have record of the actual contract data in the new system so it should be added to the year_total. I tried a normal join, left join, and right join to see if that would change the data but no luck. I tried looking online for questions like this but couldn’t find anything for this kind of situation. If anyone could point me in the right direction or knows how I can solve this issue, that would be great.

Answer

The c.status value for the efg123 contract is NULL after the left join and is filtered by c.status = 'Approved' in the WHERE clause. So try to change your SQL query like this:

SELECT 
   a.customer_id, SUM(a.contract_amount) AS year_total 
FROM Archives_Table a 
LEFT JOIN Contracts_Table c ON (a.contract_number = c.contract_number)
WHERE a.date >= '2019-01-01' AND a.date < '2020-01-01' AND a.customer_id = '1'
  AND COALESCE(c.status, 'Approved') = 'Approved' 
GROUP BY a.customer_id

fiddle

Advertisement

Answer