Mysql calculation in select statement
Solution 1
Eggyal has four good solutions. I think the cleanest way to do a running total in MySQL is using a correlated subquery -- it eliminates the group by
at the end. So I would add to the list of options:
SELECT sr.Sale_Date, sr.Stock_Delivered, sr.Units_Sold,
(select SUM(sr2.Stock_Delivered) - sum(sr2.Units_Sold)
from sales_report sr2
where sr2.sale_date <= sr.sale_date
) as StockBalance
FROM sales_report sr
ORDER BY Sale_Date
Solution 2
Generally speaking, SQL wasn't really intended to yield "running totals" like you desire. Other RDBMS have introduced proprietary extensions to deliver analytic functions which enable calculations of this sort, but MySQL lacks such features.
Instead, one broadly has four options. In no particular order:
Accumulate a running total in your application, as you loop over the resultset;
Alter your schema to keep track of a running total within your database (especially good in situations like this, where new data is only ever appended "to the end");
-
Group a self-join:
SELECT a.Sale_Date, SUM(a.Stock_Delivered) AS Stock_Delivered, SUM(a.Units_Sold) AS Units_Sold, SUM(b.Stock_Delivered - b.Units_Sold) AS `Stock Balance` FROM sales_report a JOIN sales_report b ON b.Sale_Date <= a.Sale_Date GROUP BY a.Sale_Date
-
Accumulate the running total in a user variable:
SELECT Sale_Date, Stock_Delivered, Units_Sold, @t := @t + Stock_Delivered - Units_Sold AS `Stock Balance` FROM sales_report, (SELECT @t:=0) init ORDER BY Sale_Date
![Law](https://i.stack.imgur.com/vty3g.jpg?s=256&g=1)
Comments
-
Law almost 2 years
I have been doing my office work in Excel.and my records have become too much and want to use mysql.i have a view from db it has the columns "date,stockdelivered,sales" i want to add another calculated field know as "stock balance". i know this is supposed to be done at the client side during data entry. i have a script that generates php list/report only based on views and tables,it has no option for adding calculation fields, so i would like to make a view in mysql if possible.
in excel i used to do it as follows.
i would like to know if this is possible in mysql.
i don't have much experience with my sql but i imagine first one must be able to select the previous row.colomn4 then add it to the current row.colomn2 minus current row.colomn3
If there is another way to achieve the same out put please suggest.
-
eggyal about 11 yearsThe OP wants to accumulate a running total, not simply show the day's movement.
-
Law about 11 years@Brad thanks for trying but i think, i wasn't clear before,the calculation i need is ("previous day stock balance" + "stock delivered" - "Units told" )
-
Law about 11 yearsThanks,this are very helpful insights, at least now i have an idea, am working on it.
-
Law about 11 yearsThanks, Gordon Linoff it worked perfectly.i now understand how this works. this was just a view from one of the many tables i have. i will do the same to the rest of them.i have also taken some ideas from eggyal.Thanks again i had been on this now for 2days.
-
Brad about 11 yearsAhh..sorry 'bout that - I'm happy you did get the right answer, though!
-
peterh about 9 yearsAlthough the code is appreciated, it should always have an accompanying explanation. This doesn't have to be long but it is expected.