Returning sum of group of rows with each row

Question

Can the sum of column values for rows meeting selection criteria be returned not only with each individual row but for multiple groups meeting the same criteria?

For example, consider:

id  order_id    quantity
1   1           1
2   1           3
3   1           5
4   2           2
5   2           4

What MySQL SELECT query could return the follow results:

id  order_id    quantity    order_sum
1   1           1           9
2   1           3           9
3   1           5           9
4   2           2           6
5   2           4           6

EDIT: follow-up question: assuming the results are produced from a query with multiple conditions, how would these conditions be handled in the final query?

Answer 1

You can use a subquery in the "from" clause:

select  t.*, tsum.sumquantity
from t join
     (select t.orderid, sum(quantity) as sumquantity
      from t
      group by t.order_id
     ) tsum
     on t.orderid = tsum.orderid

The advantage of putting it in the "from" clause is that you can have multiple variables. For instance, you could add the number of orders, the maximum quantity, the number of times exactly 1 quantity appears, and so on.