CODEWITHSUNDEEP
sql-server
Fabio
Fabio

Reputation: 32445

SUM from multiple tables

I have three tables:

Then I need SUM(of orderline.price) and SUM(of orderactions.price) per orderID.

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?

Upvotes: 1

Views: 862

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

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 JOIN.

Upvotes: 1

httpskk
httpskk

Reputation: 19

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

Upvotes: 1

Related Questions