Resultant from 2 Tables not fully linked.

[#1]

Hi All,

I have 2 Tables - Table 1 and Table 2

In Table 1 - Mapping Table,

There are 4 fields

Mapping1	Mapping2	Value1	Value2
A	AA	99.00%	344
B	BB	89.00%	45
C	CC	95.00%	55
D	AA	94.50%	65
E	CC	98.00%	43
F	DD	99.50%	436
G	EE	98.50%	655

Two of them - Mapping1 and Mapping2 are used to Map different tables.

Also Combination of Mapping1 and Mapping2 forms a uniqueID.

In Table2 - Main Table,

There are 6 fields

ID	Column1	Col2_Mapping1	CompanyName	Column3	Column4
1	4	Z	Company1	16	64
2	5	B	Company2	20	100
3	6	A	Company3	24	144
4	7	G	Company4	28	196
5	2	J	Company5	8	16
6	5	D	Company2	20	100
7	3	K	Company4	12	36
8	5	G	Company2	20	100

ID is the primary key,

Column1 is the data pertaining to UniqueID

Col2_Mapping1 has the mapping same as Mapping1  in Table1

Column3 is derived from Column1

Column4 is derived from Column1 and Column3

Required Output:    

Mapping1	Mapping2	Value1	Value2	Sum(Column1) from Table 2	Count(Column4) from Table 2	Sum(Column3) from table 2
A	AA	99.00%	344
B	BB	89.00%	45
C	CC	95.00%	55
D	AA	94.50%	65
E	CC	98.00%	43
F	DD	99.50%	436

Mapping1 From Table1

Mapping2 From Table1

Value1 From Table1

Value2 From Table1

Sum(Column1) From Table2  ---- How to get this?

Count(Column4) From Table2  ---- How to get this?

Sum(Column3) From Table2  ---- How to get this?

if any Mapping1 (or Col2_Mapping1 in Table 2) doesn't have any value in Column1 then it should be 0.

Attached Sample data.

Hi stalwar1‌ , Please check if you can help.

Regards,

Anjali Gupta

[#2]

Thanks a lot Kushal. This worked for me.

Regards,

Anjali Gupta

[Kushal_Chawda]

awesome