SUM from multiple tables
I have three tables:
Then I need
SUM(of orderline.price) and
SUM(of orderactions.price) per
When I used:
SELECT order.ID, SUM(orderlines.price), SUM(orderactions.price) FROM order LEFT JOIN orderlines ON orderlines.orderID=order.ID LEFT JOIN orderactions ON orderactions.orderID=order.ID WHERE order.ID=@orderID GROUP BY order.ID
I got a result from
orderactions which a equivalent to
SUM(orderactions.price)*quantity of orderlines for this order.ID
Only solution I found, which given me a right result, are sub-query for every “SUM”-table:
SELECT order.ID , (SELECT SUM(orderlines.price) FROM orderlines WHERE orderlines.orderId=order.ID) , (SELECT SUM(orderactions.price) FROM orderactions WHERE orderactions.orderId=order.ID) FROM order WHERE order.ID=@orderID
Question: is there some other (faster) solution for this, because sub-queries are slow and we try to not using them?
2 Solutions collect form web for “SUM from multiple tables”
Since your select only runs the two very simple subqueries once each, I’d say you’re pretty close to optimal for your query. There is no need to add a
JOIN unless the queries are correlated in some way.
The slow part about subqueries is usually that the database may (if you’re not careful how you write your query) non obviously execute them more than once. This normally happens if they depend on external values that may change per row, ie when you
JOIN them in some way.
Your subqueries are straight forward enough and not using any external values other than your constant, so in your case, they only execute once each, and there is nothing in them that is simplified by adding a
depends of your data this way can be a solution:
SELECT order.ID, SUM(CASE WHEN orderlines.orderID IS NOT NULL THEN orderlines.price ELSE 0 END), SUM(CASE WHEN orderactions.orderID IS NOT NULL THEN orderactions.price ELSE 0 END) FROM order LEFT JOIN orderlines ON orderlines.orderID=order.ID LEFT JOIN orderactions ON orderactions.orderID=order.ID WHERE order.ID=@orderID GROUP BY order.ID