MySQL Cumulative aggregation

Aggregate aggregation is the aggregation of data from the first element in the sequence to the current element, such as the number of orders and the average order quantity that is returned from the beginning of each month to the present for each employee

The line number problem has two solutions, which are for using subqueries and using connections. Subqueries are often more intuitive and readable. However, when aggregation is required, the subquery needs to scan the data once for each aggregation, and the connection method usually only needs to be scanned once to get the results. The following query uses a connection to get the results

SELECT  A.empid,  a.ordermonth,a.qty as Thismonth,  SUM (b.qty) as Total,  CAST (AVG (B.qty) as DECIMAL (5,2 ) as Avgfrom emporders a INNER JOIN emporders B on    a.empid=b.empid and    b.ordermonth <= A.ordermonthgroup by a. Empid,a.ordermonth,a.qtyorder by A.empid,a.ordermonth

If you are only querying a cumulative order for 2015 years, you can add a where condition

WHERE date_format (a.ordermonth, '%Y ') = ' + ' and date_format (B.ordermonth, '%Y ') = ' 2015 '

The results are as follows. You may also want to filter the data, for example, to return only the monthly orders for each employee before reaching a goal. This assumes that the total number of orders per employee is up to 1000. Here you can use the havingfilter to complete the query

SELECT  A.empid,  a.ordermonth,a.qty as Thismonth,  SUM (b.qty) as Total,  CAST (AVG (B.qty) as DECIMAL (5,2 ) as Avgfrom emporders a INNER JOIN emporders B on    a.empid=b.empid and    b.ordermonth <= a.ordermonthwhere date_ FORMAT (A.ordermonth, '%Y ') = ' + ' and date_format (B.ordermonth, '%Y ') = ' + ' GROUP by A.empid,a.ordermonth, A.qtyhaving Total<1000order by A.empid,a.ordermonth

There is no statistics on the situation in the month of 1000, and if statistics are to be counted, the situation is somewhat complicated. If total <= 1000 is specified, only the number of orders for that month is exactly 1000 to be counted, otherwise the month will not be counted. So the filtering of this problem can be considered from another aspect. When the cumulative cumulative order is less than 1000, the difference between the cumulative order and the last month's order is less than 1000, and the month of the first order quantity over 1000 is also counted. So the SQL statement for this solution is as follows

SELECT  A.empid,  a.ordermonth,a.qty as Thismonth,  SUM (b.qty) as Total,  CAST (AVG (B.qty) as DECIMAL (5,2 ) as Avgfrom emporders a INNER JOIN emporders B on    a.empid=b.empid and    b.ordermonth <= a.ordermonthwhere date_ FORMAT (A.ordermonth, '%Y ') = ' + ' and date_format (B.ordermonth, '%Y ') = ' + ' GROUP by A.empid,a.ordermonth, A.qtyhaving Total-a.qty < 1000ORDER by a.empid,a.ordermonth

The result of the operation is as follows if you want to return only the data for the current month with a cumulative order number of 1000 and do not return the previous month, you can further filter the above SQL statement and add a condition with a cumulative order quantity greater than or equal to 1000. The SQL statement for this issue is as follows,

SELECT  A.empid,  a.ordermonth,a.qty as Thismonth,  SUM (b.qty) as Total,  CAST (AVG (B.qty) as DECIMAL (5,2 ) as Avgfrom emporders a INNER JOIN emporders B on    a.empid=b.empid and    b.ordermonth <= a.ordermonthwhere date_ FORMAT (A.ordermonth, '%Y ') = ' + ' and date_format (B.ordermonth, '%Y ') = ' + ' GROUP by A.empid,a.ordermonth, A.qtyhaving Total-a.qty <->= 1000ORDER by A.empid,a.ordermonth

The operation results are as follows

MySQL Cumulative aggregation