ketan ketan - 2 months ago 15
SQL Question

Query to Group by multiple table data in SQL Server

I have 3 tables containing data as below and I want to group data into single output.


table1

pid val

1 1

2 2




table2

id pid val

1 1 1

2 1 2

3 2 1




table3

id pid val

1 1 1

2 1 2

3 2 1

4 2 1



    
Required Output:

pid output

1 1 (table1 val + table2 Sum(val) - table3 Sum(val))

2 1 (table1 val + table2 Sum(val) - table3 Sum(val))



Please help with optimal query which can achieve this without any temp table.

Answer

Assume that pid is primary key of table1, you could use this

SELECT 
    t1.pid,
    t1.val + t2.sumval - t3.sumval
FROM
    table1 t1
INNER JOIN
    (SELECT 
        pid, SUM(val) as sumval
    FROM 
        table2
    GROUP BY 
        pid) t2
ON 
    t2.pid = t1.pid
INNER JOIN
    (SELECT 
        pid, SUM(val) as sumval
    FROM 
        table3
    GROUP BY 
        pid) t3
ON 
    t3.pid = t1.pid;
Comments