Lecture
SELECT DATE (dateadd) dt,
SUM (case when typesite = 1 && odobr = 1 then 1 else 0 end) a1,
GROUP_CONCAT (case when odobr = 0 then id else NULL end) as ids,
SUM (case when typesite> 0 then 1 else 0 end) tot,
(SELECT sum (case when typesite> 0 then 1 else 0 end) from bill t where DATE (t.dateadd) <= DATE (b.dateadd)) AS incc,
FROM bill b
GROUP BY DATE (dateadd) DESC
WITH ROLLUP
1. There is an aggregated table and it is necessary to consider the accruing result.
the answer is that you can use a subquery for example - not efficiently, but clearly
2. To count the total of each slab, use WITH ROLLUP
Starting in MySQL 4.1.1, the GROUP BY clause provides the WITH ROLLUP modifier, which adds a few extra lines to the final output. These lines represent the final operations of the highest level (superagregate). ROLLUP, thus, allows you to answer questions at many levels of analysis within a single request. This can be used, for example, to represent support for OLAP operations (Online Analytical Processing).
When you use ROLLUP, you cannot use the ORDER BY clause at the same time to sort the results. In other words, ROLLUP and ORDER BY are mutually exclusive. However, you still have a certain opportunity to influence the sorting order. In MySQL, the GROUP BY clause sorts the results, and you can explicitly specify the ASC and DESC keywords for the columns listed in the GROUP BY clause to specify the sort order of the individual columns.
(The top-level summary lines added by ROLLUP still appear after the lines by which they are calculated, regardless of the sort order.)
approximate values
2015-04-09 | eight | 0 | 242 | 1004 |
2015-04-08 | ten | 0 | 211 | 874 |
2015-04-07 | 6 | 0 | 222 | 694 |
2015-04-06 | eight | 0 | 191 | 453 |
2015-04-05 | one | 0 | 127 | 234 |
2015-04-04 | four | 0 | 147 | 147 |
43 | 0 | 733 | 147 |
but it will be much faster once in 10 if you use user variables and a subquery once
eg
SELECT Y. *,
IF (@s> 0, @s, 0) AS incc,
@s: = @ s- Y.tot AS tmp
FROM
(
SELECT @s: = SUM (price) a, DATE (dateadd) dt, SUM (price) tot
FROM bill b
GROUP BY DATE (dateadd) DESC
WITH ROLLUP
) Y
Comments
To leave a comment
Databases - MySql (Maria DB)
Terms: Databases - MySql (Maria DB)