Pierre001 Pierre001 - 5 months ago 14
SQL Question

HAVING SUM issue

I have a question about the statement HAVING SUM.

My table:

id int(11) AUTO_INCREMENT
date date
number int(3)

+----+------------+--------+
| id | date | number |
+----+------------+--------+
| 1 | 2010-01-02 | 0 |
| 2 | 2010-01-03 | 3 |
| 3 | 2010-01-04 | 0 |
| 4 | 2010-01-05 | 2 |
| 5 | 2010-01-06 | 1 |
| 6 | 2010-01-07 | 3 |
+----+------------+--------+


I would like return the date when the
SUM
of number is 6 and
date
>
'2010-01-04'


I want return the date when the addition of the field number is equal to 6 (after a specific date, 2010-01-04 in this example). Example : my query should return 2010-01-07 because 2 in 2010-01-05 1 in 2010-01-06 3 in 2010-01-07

I've tested this query without success:

SELECT date, SUM(number) AS total FROM MyTable
GROUP BY date
HAVING SUM(number) = 6

Answer
select date 
    from 
      (select date, @n:=@n+total total 
          from 
            (select date, sum(number) total 
                from MyTable 
              where date > '2010-01-04' 
              group by date 
              order by date 
            ) t 
           cross join 
            (select @n:=0) n 
      ) tt 
   where total = 6