Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Join and the Aggregate Puzzle ]

The puzzle is very simple. We have 3 tables named DJ, DJ1, DJ2. We have get aggregated values of Val from table DJ1 and DJ2 . Please check out the sample input and expected output for details.

Sample Input

DJ

ID
1
2
3
4

DJ1

ID Val
1 100
2 200
3 500
3 500
2 100

DJ2

ID Val
2 10000
3 500
2 100
4 100
4 900

Expected output

ID Val1 Val2
1 100 0
2 300 10100
3 1000 500
4 0 1000

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE DJ
(
ID INT
)
GO
INSERT INTO DJ(ID) Values (1),(2),(3),(4)
GO

CREATE TABLE DJ1
(
ID INT
,Val INT
)
GO
INSERT INTO DJ1(ID,Val) VALUES (1,100),(2,200),(3,500),(3,500),(2,100)
GO

CREATE TABLE DJ2
(
ID INT
,Val INT
)
GO
INSERT INTO DJ2(ID,Val) VALUES (2,10000),(3,500),(2,100),(4,100),(4,900)
GO
--

UPDATE – 10-Apr-2015 – Solution 1 


-- Solution 1 --

SELECT d.ID
, ISNULL((SELECT SUM(d1.Val) FROM DJ1 d1 WHERE d1.ID = d.ID ),0) Val1
, ISNULL((SELECT SUM(d2.Val) FROM DJ2 d2 WHERE d2.ID = d.ID ),0) Val2
FROM DJ d

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com