[SOLVED] Incremental results

[fragerdaz]

My table:

field1: id (auto_increment)

field2: date

 

My query:

SELECT count(name), date FROM table GROUP BY date

 

This returns an array with each date and the number of occurences of that date.

 

My problem:

I need to increment each new date with the previous results.  Here's an example:

 

date | count(id) | what I need

------------------------------

2007-01-01 | 3 | 3    << 3 rows with a date of 2007-01-01

2007-04-02 | 7 | 10  << 7 rows with a date of 2007-04-02 (I need the current total of 10)

2007-04-06 | 5 | 15  << 5 rows with a date of 2007-04-06 (I need the current total of 15)

2007-07-28 | 1 | 16  << 1 row with a date of 2007-07-28 (I need the current total of 16)

and so on...

 

 

How can I accomplish this?

[vijayfreaks]

Hi..

        try this query:

        SELECT sum(count(id)) as sum, date FROM table GROUP BY date

 

Regards,

Vijay

[Illusion]

try this

 

Select date,(a.count(name)+b.count(name)) whatuneed from table a,table b where a.id=b.id-1 group by date;

 

with out using MySQL

 

whatuneed=0;

while(Conditon)

{

whatuneed=whatuneed+Count;

}

[fragerdaz]

Thanks for your replies, but none of the proposed solutions worked.  :-\

 

Here's how I got it working:

 

$query = "SELECT date, count(name) FROM table GROUP BY date";
if ($result = $mysqli->query($query)) {
	while ($obj = $result->fetch_assoc()) {
		$data[] = $obj;
	}
	$sum = "0";
	for ($i = 0; $i < count($data); $i++) {
		$sum += $data[$i]['count(name)'];
		$data[$i]['count(name)'] = $sum;
	}
	return $data;

 

This returns an array of distinct dates with the incremental number of rows for each date.