MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Archives_Table(
  customer_id     INT  NOT NULL
  ,contract_amount NUMERIC(17,2) NOT NULL
  ,date            DATE  NOT NULL
  ,contract_number VARCHAR(17) NOT NULL
);
INSERT INTO Archives_Table(customer_id,contract_amount,date,contract_number) VALUES
 (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');

CREATE TABLE Contracts_Table(
  customer_id     INT  NOT NULL
  ,contract_amount NUMERIC(17,2) NOT NULL
  ,date_authorized DATE  NOT NULL
  ,contract_number VARCHAR(17) NOT NULL
  ,status          VARCHAR(10) NOT NULL
);
INSERT INTO Contracts_Table(customer_id,contract_amount,date_authorized,contract_number,status) VALUES
 (1,300.00,'2019-01-03','abc123','Approved')
,(1,250.00,'2019-01-06','abc456','Cancelled')
,(1,320.00,'2019-02-01','abc789','Approved');
select * from  Archives_Table;
select * from Contracts_Table;


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




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


-- Your join result
select 
 *
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'


customer_id
contract_amount
date
contract_number
customer_id
contract_amount
date_authorized
contract_number
status


1
300.00
2019-02-01
abc123
1
300.00
2019-01-03
abc123
Approved

1
320.00
2019-04-02
abc789
1
320.00
2019-02-01
abc789
Approved


-- JOIN result before SUM
select 
   *
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'


customer_id
contract_amount
date
contract_number
customer_id
contract_amount
date_authorized
contract_number
status


1
300.00
2019-02-01
abc123
1
300.00
2019-01-03
abc123
Approved

1
250.00
2019-02-03
abc456
1
250.00
2019-01-06
abc456
Cancelled

1
320.00
2019-04-02
abc789
1
320.00
2019-02-01
abc789
Approved

1
230.00
2019-07-03
efg123
null
null
null
null
null


SELECT 
   a.customer_id, 
   SUM(CASE WHEN c.status IS NULL OR c.status = 'Approved' THEN a.contract_amount END) 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'
GROUP BY a.customer_id


customer_id
year_total


1
850.00


-- Or 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


customer_id
year_total


1
850.00

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

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