This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output for calculating SUM between two tables in SQL Server.
Input Data for table T1:
|
1 2 3 4 5 6 7 |
Num Code ----------- ---- 1 a 4 b 5 z 8 c 10 d |
Input Data for table T2:
|
1 2 3 4 5 6 7 8 |
Num Code ----------- ---- 44 a 16 b 10 c 26 z 30 d 20 x |
Expected Output:
|
1 2 3 4 5 6 7 8 |
TotalNum Code ----------- ---- 45 a 20 b 18 c 40 d 20 x 31 z |
Create two sample tables:
|
1 2 3 4 5 6 7 |
CREATE TABLE T1 (Num INT, Code CHAR(1)) GO CREATE TABLE T2 (Num INT, Code CHAR(1)) GO |
Insert sample records:
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO T1 VALUES (1,'a'),(4,'b'),(5,'z') ,(8,'c'),(10,'d') GO INSERT INTO T2 VALUES (44,'a'),(16,'b'),(10,'c') ,(26,'z'),(30,'d'),(20,'x') GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 |
;WITH CTE AS ( SELECT Num, Code FROM T1 UNION ALL SELECT Num, Code FROM T2 ) SELECT SUM(Num) as TotalNum, Code FROM CTE GROUP BY Code |
Please try the different solution for this puzzle and share it via comment...
