Menu
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?
571
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table.
SUM is used with a GROUP BY clause. The aggregate functions summarize the table data. Once the rows are divided into groups, the aggregate functions are applied in order to return just one value per group. It is better to identify each summary row by including the GROUP BY clause in the query resulst.
If you know the values to always be the same, then one simple (although not necessarily optimized) solution is to wrap your original GROUP BY query in another query (making the original a subquery) and then use the SUM function in the outer query, without a GROUP BY clause.
To sum rows with same ID, use the GROUP BY HAVING clause.
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.
82
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
