How can i achieve this. T2 is linked with another table which contains order details like customer name, country and classification. They have an inner join.
T1 is linked to T2 only via order code and order item.
3 Answers
Assuming that both tables report the same set of order numbers, we can try joining two subqueries each of which finds the sums in the respective tables:
SELECT
t1.ORDER_NUM,
t1.ORDER_ITEM,
t1.PRODUCED + t2.PRODUCED AS PRODUCED
FROM
(
SELECT ORDER_NUM, ORDER_ITEM, SUM(PRODUCED) AS PRODUCED
FROM table1
GROUP BY ORDER_NUM
) t1
INNER JOIN
(
SELECT ORDER_NUM, ORDER_ITEM, SUM(NET_IN - NET_OUT) AS PRODUCED
FROM table2
GROUP BY ORDER_NUM
) t2
ON t1.ORDER_NUM = t2.ORDER_NUM AND
t1.ORDER_ITEM = t2.ORDER_ITEM
ORDER BY
t1.ORDER_NUM,
t1.ORDER_ITEM;
Note that the above is not necessarily an ideal approach, because a given order/item combination in one table might not appear in the other table. A better approach would be to start the query with a reference table containing all orders and items. That failing, we could convert the above to a full outer join.
Comments
I think a simple approach is union all:
select ordernum, orderitem, sum(produced) as produced
from ((select ordernum, orderitem, produced
from table1
) union all
(select ordernum, orderitem, netout
from table2
)
) t12
group by ordernum, orderitem;
This has two advantages over pre-aggregating and using joins:
- It keeps all order/item pairs, even those that appear in one table.
- If you add a
whereclaus to the outer query, SQL Server is likely to "project" that into the subqueries.
1 Comment
Saad Azhar
But in this query net in column has not been used.
Try for bellow query also
select t1.order_num,t1.order_item,sum(t1.produced)+(select sum(net_in) from t2)-(select sum(t2.net_out) from t2)PRODUCED
from t1
group by t1.order_num,t1.order_item
if you have wanted the only sum from another table that time you have used select query and do the sum of a particular column.
Help others reproduce the problem