You get a bonus - 1 coin for daily activity. Now you have 1 coin

the cumulative total of mysql and total for each column

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
If you have any suggestion, idea, thanks or comment, feel free to write. We really value feedback and are glad to hear your opinion.
To reply

Databases - MySql (Maria DB)

Terms: Databases - MySql (Maria DB)