SQL Server | sql server interview questions and answers | Total by multiple tables | Part 39

For SQL Quiz-

Find Us On FaceBook-

Query---

CREATE TABLE PART39_A
(
ID INT,
SALARY INT
)

CREATE TABLE PART39_B
(
ID INT,
SALARY INT
)

CREATE TABLE PART39_C
(
ID INT,
SALARY INT
)

INSERT INTO PART39_A VALUES(1,100),(2,200),(3,300),(4,400)
INSERT INTO PART39_B VALUES(1,100),(2,200),(3,300)
INSERT INTO PART39_C VALUES(1,100),(2,200),(3,300),(5,500)

select * from PART39_A
select * from PART39_B
select * from PART39_C
---method 1
;with cte
as
(
select * from PART39_A
union all
select * from PART39_B
union all
select * from PART39_C
)
select id,sum(SALARY) as sal
from cte
group by ID

--Method 2

from PART39_A as a
full join PART39_B as b on a.ID=b.ID
full join PART39_C as c on a.ID=c.ID

[roshnisingh]

Wow it's very easy to understand..
Thanks for sharing..
Please share more videos like this..

[vijay.s-llyq]

Method-1
select id, sum(salary) from
(select id, salary from PART39_A
union ALL
select id, salary from PART39_B
union ALL
select id, salary from PART39_C)ddsf
group by id


Method-2

select id, SUM(CASE
WHEN ID =1 THEN SALARY
WHEN ID =2 THEN SALARY
WHEN ID =3 THEN SALARY
WHEN ID =4 THEN SALARY
WHEN ID =5 THEN SALARY
END) AS SUM_OF_SALARY
from
(select id, salary from PART39_A
union ALL
select id, salary from PART39_B
union ALL
select id, salary from PART39_C)ddsf
group by id