Accumulate results of a COUNT

Question

Accumulate results of a COUNT

Navigation

#1 by (2 votes)

3

I have a product sales table with fields sales_id , date and product . This query:

    SELECT DAYOFYEAR(fecha),COUNT(*) as con FROM ventas WHERE YEAR(fecha)=2016 GROUP BY DAYOFYEAR(fecha)

I get the following result:

DAYOFYEAR(fecha) con
1                 4
2                 5
3                 2
4                 1
5                 1
6                 4

The question is: What would be the SQL query so that the result is as follows?

DAYOFYEAR(fecha) con
1                 4
2                 9
3                 11
4                 12
5                 13
6                 17

I mean how to accumulate the results that are coming out in the COUNT?

Thank you very much.

mysql sql

asked by Yuri 24.07.2016 в 21:05

source

1 answer

How can I put a background image in an ImageView without it being blurred? Force download or show in another window a pdf using html2pdf

score 2 · Accepted Answer

One option would be to calculate the sum with a subquery to the sales table itself where all rows with a date less than or equal to the current one are counted. Something like this:

SELECT DAYOFYEAR(fecha), 
       (
        SELECT COUNT(*) FROM venta b WHERE YEAR(b.fecha)=2016 AND b.fecha <= a.fecha
       ) AS cuenta
FROM   venta a 
WHERE YEAR(fecha)=2016 
GROUP BY DAYOFYEAR(fecha)