SQL Server 2019

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

-- First table

CREATE TABLE dbo.Action
(
ActionWeek date, 
ActionYear int,
Region nvarchar(50), 
Action nvarchar(50), 
ActionAmount int, 
Frequency int);

INSERT INTO Action values
('2019-01-01' , 2019, 'Wales', 'abc123', 123, 1),
('2019-03-10' , 2019, 'Wales', 'abc123', 132, 2),
('2019-06-01' , 2019, 'Wales', 'abc123', 153, 3),
('2020-01-11' , 2020, 'Wales', 'abc123', 123, 1),
('2019-01-01' , 2019, 'Australia', 'abd133', 723, 1),
('2019-08-01' , 2019, 'Australia', 'abd133', 763, 2),
('2020-04-01' , 2020, 'Australia', 'abd133', 823, 1),
('2019-01-01' , 2019, 'Mexico', 'cbc423', 3123, 1),
('2020-03-01' , 2020, 'Mexico', 'cbc423', 3223, 1),
('2020-05-01' , 2020, 'Mexico', 'cbc423', 3423, 2),
('2020-08-21' , 2020, 'Mexico', 'cbc423', 3623, 3),
('2021-01-01' , 2021, 'Mexico', 'cbc423', 3723, 1),
('2021-09-01' , 2021, 'Mexico', 'cbc423', 3823, 2)

-- Second Table
CREATE TABLE dbo.Response
(
ResponseYear int,
ResponseAction nvarchar(50),
ResponseDate date, 
ResponseWeek date, 
ResponseValue float,
);


INSERT INTO Response values
(2019, 'abc123', '2019-02-04', '2019-02-01', 50),
(2019, 'abc123', '2019-02-08', '2019-02-08', 25),
(2019, 'abc123', '2019-09-24', '2019-09-24', 100),
(2020, 'abc123', '2020-01-20', '2019-01-23', 5),
(2021, 'abc123', '2021-06-10', '2021-06-07', 3),
(2019, 'abd133', '2019-01-10', '2019-01-07', 15),
(2019, 'abd133', '2019-02-10', '2019-02-04', 25),
(2019, 'abd133', '2019-03-10', '2019-03-04', 35),
(2021, 'cbc423', '2021-01-13', '2021-01-11', 77)

22 rows affected

-- I've tried various ways and keep failing. 
-- This is the most simplist version of the query

SELECT  A.ActionWeek, A.Action, A.Frequency
	, (SELECT SUM(R.ResponseValue)
	) AS DonationValue
	FROM Action A
JOIN Response R ON A.Action = R.ResponseAction 
GROUP BY A.ActionWeek, A.Action, A.Frequency
ORDER BY A.ActionWeek, A.Action, A.Frequency






ActionWeek
Action
Frequency
DonationValue


2019-01-01
abc123
1
183

2019-01-01
abd133
1
75

2019-01-01
cbc423
1
77

2019-03-10
abc123
2
183

2019-06-01
abc123
3
183

2019-08-01
abd133
2
75

2020-01-11
abc123
1
183

2020-03-01
cbc423
1
77

2020-04-01
abd133
1
75

2020-05-01
cbc423
2
77

2020-08-21
cbc423
3
77

2021-01-01
cbc423
1
77

2021-09-01
cbc423
2
77


-- Desired results would be
A.ActionWeek / A.Action / A.Frequency / R.ResponseValue
2019-01-01 / abc123 / 1 / 75
2019-03-10 / abc123 / 2 / 0
2019-06-01 / abc123 / 3 / 100
2020-01-11 / abc123 / 1 / 8
2019-01-01 / abd133 / 1 / 75
2019-08-01 / abd133 / 2 / 0
2020-04-01 / abd133 / 1 / 0
2019-01-01 / cbc423 / 1 / 0
2020-03-01 / cbc423 / 1 / 0
2020-05-01 / cbc423 / 2 / 0
2020-08-21 / cbc423 / 3 / 0
2021-01-01 / cbc423 / 1 / 77
2021-09-01 / cbc423 / 2 / 0
Msg 102 Level 15 State 1 Line 2
Incorrect syntax near '/'.

ActionWeek	Action	Frequency	DonationValue
2019-01-01	abc123	1	183
2019-01-01	abd133	1	75
2019-01-01	cbc423	1	77
2019-03-10	abc123	2	183
2019-06-01	abc123	3	183
2019-08-01	abd133	2	75
2020-01-11	abc123	1	183
2020-03-01	cbc423	1	77
2020-04-01	abd133	1	75
2020-05-01	cbc423	2	77
2020-08-21	cbc423	3	77
2021-01-01	cbc423	1	77
2021-09-01	cbc423	2	77