How to join two tables where Columns are not matching in SQL

I have two different tables and in the select query I need to bring the SUM of the other column but both the tables have some similar data but still in the query output values are not coming fine.

I am using below query :-

Query 1

select COUNTRY, SUM (BUDGET_SALES) AS BUDGET_SALES

 from ANALYSE.LCM_COMMON_BUDGET_SALES

 WHERE WEEK_START_DATE=@WEEK_COMMENCING_VAR

group by COUNTRY

Query 2

select distinct cntry AS COUNTRY, SUM (B.BUDGET_SALES) AS BUDGET
 from [publish].[LCM_COMMON_HNB_WEEKLY_ACTUAL] A
 
left join ANALYSE.LCM_COMMON_BUDGET_SALES B

ON --A.bnnr=B.BANNER 
A.cntry=B.COUNTRY 
--and A.Week_End=B.WEEK_END_DATE
and A.Week_Commencing=B.WEEK_START_DATE

WHERE A.Week_Commencing=@WEEK_COMMENCING_VAR
group by cntry

Query 1 output

COUNTRY	BUDGET_SALES
MY	20463355.16
SG	41095952.67

Query 2 output

COUNTRY	BUDGET
HK	NULL
SG	7767135054.63
ID	NULL
MO	NULL
MY	12441719937.28

How can this be fixed so that the Query 2 shows the BUDGET same as BUDGET_SALES of Query 1

Sample of table [publish].[LCM_COMMON_HNB_WEEKLY_ACTUAL] :-

COUNTRY	BANNER	BANNER_DESC	SITE	WEEK_START_DATE	WEEK_END_DATE	BUDGET_SALES
HK	FOOD	LEVEN	3314	2020-11-23	2021-01-03	13538.40
HK	FOOD	LEVEN	3126	2020-11-23	2021-01-03	7403.63

Sample of table ANALYSE.LCM_COMMON_BUDGET_SALES :-

COUNTRY	BANNER	Week_Commencing	Week_End
HK	HN	2020-11-23	2020-11-29
HK	HN	2020-11-23	2020-11-29

EXPECTED OUTPUT :-

COUNTRY	SUM_BUDGET	Week_Commencing
MY	20463355.16	2021-01-04
SG	41095952.67	2021-01-04

Answer

What is most likely is that your table [publish].[LCM_COMMON_HNB_WEEKLY_ACTUAL] has a different granularity (likely lower) than your table ANALYSE.LCM_COMMON_BUDGET_SALES so this what I would do:

1. Get to the same grain first – Country and Week. You should verify independently that the country weekly budget amounts meet expectations

With CountryWeeklyActual as
(
  select Country, WEEK_START_DATE, SUM(BUDGET_SALES) as ActualBudgetSum
  from [publish].[LCM_COMMON_HNB_WEEKLY_ACTUAL]
  where WEEK_START_DATE = @WEEK_COMMENCING_VAR
  group by Country, WEEK_START_DATE
), CountryWeeklyCommon as
(
  select COUNTRY, Week_Commencing, SUM(BUDGET_SALES) as CommonBudgetSum
  from ANALYSE.LCM_COMMON_BUDGET_SALES
  where Week_Commencing = @WEEK_COMMENCING_VAR
  Group by COUNTRY, Week_Commencing 
)

2. Join at the same level of granularity

select wa.COUNTRY, wa.WEEK_START_DATE, wa.ActualBudgetSum, wc.Week_Commencing, wc.CommonBudgetSum
From CountryWeeklyActual wa 
left join CountryWeeklyCommon wc
on wa.COUNTRY=wc.Country and wa.WEEK_START_DATE=wc.Week_Commencing

From here you can decide what fields you need to include.