Calculate current amount in MySQL

Question

I have this MySQL query:

SELECT DAYOFYEAR(`date`) AS d, COUNT(*) FROM `orders` WHERE `hasPaid` > 0 GROUP BY d ORDER BY d 

Which returns something like this:

 d | COUNT(*) | 20 | 5 | 21 | 7 | 22 | 12 | 23 | 4 | 

I would really like for another column at the end to display the current amount:

 d | COUNT(*) | ??? | 20 | 5 | 5 | 21 | 7 | 12 | 22 | 12 | 24 | 23 | 4 | 28 | 

Is it possible?

Answer 1

Perhaps this is a simpler solution for you and does not allow the database to perform a ton of queries. This performs only one query, and then does a little math on the results in a single pass.

 SET @runtot:=0; SELECT q1.d, q1.c, (@runtot := @runtot + q1.c) AS rt FROM (SELECT DAYOFYEAR(`date`) AS d, COUNT(*) AS c FROM `orders` WHERE `hasPaid` > 0 GROUP BY d ORDER BY d) AS q1 

This will give you an extra RT column (current shared). Do not skip the SET statement above to initialize the current shared variable first, or you will get a column of NULL values.

Answer 2

 SELECT DAYOFYEAR(O.`date`) AS d, COUNT(*), (select count(*) from `orders` where DAYOFYEAR(`date`) <= d and `hasPaid` > 0) FROM `orders` as O WHERE O.`hasPaid` > 0 GROUP BY d ORDER BY d 

This will require syntax tuning (I don't have MySQL to test it), but it shows you this idea. The subquery just has to go back and add everything fresh that you already included in the external query, and you need to do this for each row.

Take a look at this question on how to use connections to accomplish the same thing.

To solve performance degradation issues with data growth: since there is max. 366 days a year, and I assume that you have not fulfilled this request for several years, the subquery will be evaluated up to 366 times. With the correct hasPaid date and flag indexes, you'll be fine.

Answer 3

Starting with MySQL 8, you will use window functions for this type of query:

 SELECT dayofyear('date') AS d, count(*), sum(count(*)) OVER (ORDER BY dayofyear('date')) FROM 'orders' WHERE 'hasPaid' > 0 GROUP BY d ORDER BY d 

In the above query, the aggregate function count(*) embedded in the window function sum(..) OVER (..) , which is possible due to the logical order of operations in SQL . If this is too confusing, you can easily use a view or WITH clause to better structure your query:

 WITH daily (d, c) AS ( SELECT dayofyear('date') AS d, count(*) FROM 'orders' WHERE 'hasPaid' > 0 GROUP BY d ) SELECT d, c, sum(c) OVER (ORDER BY d) ORDER BY d 

Answer 4

I would say that it is not possible for each resulting row to be independent. Use a programming language to get these values.

Answer 5

If you do not have another option, but if you do it in sql, I would summarize the results in the programming language that makes the request. Such a game will be very slow as the table grows.

Answer 6

You can hack this using the Cross Join statement or some slaf connections, but it will slow down with any large datasets, so this is probably best done in the mail request handler; either client code cursor

Answer 7

This is one of the only places where cursors are faster than dial-based queries, if performance is critical, I would either

• Do it outside of mysql or
• Using MySql 5 Cursors

Answer 8

You can calculate the current balance using a temporary table in MySQL. The following query should work:

 CREATE TEMPORARY table orders_temp1 (SELECT id, DAYOFYEAR('date') AS d, COUNT(*) as total FROM 'orders' WHERE 'hasPaid' > 0 GROUP BY d ORDER BY d); CREATE TEMPORARY table orders_temp2 (SELECT * FROM orders_temp1); SELECT d, total, (SELECT SUM(t2.total) FROM orders_temp2 t2 WHERE t2.id<=t1.id) as running_total FROM orders_temp1 t1; 

The temporary table is used to organize the query. Please note that the temporary table only exists for the duration of the connection to the MySQL server.

The above query uses a subquery that returns the balance of all the rows in the temporary table before and including the current row. The balance is assigned to the current row in the actual table