A difficult challenge-- How to calculate sum of earlier date records for each li

[laokn]

Can you help me with the following Mysql question?

I get a table, say, three variables: ID, Date and Value, for example,

  ID Date Value

1 2001  2

2 2005  3

3 1986  7

4 1985  1

How can I add a variable, sum, to each line, with the condition that sum=sum the value of all the records that are early than the record’s date

 

For the same sample on the top, I want the result turns out to be:

 

ID Date Value Sum

1 2001  2  8

2 2005  3  10

3 1986  7  1

4 1985  1  0

 

The sum of the ID 1 is 8, because only the dates of ID 3 (1986) and 4 (1985) are earlier than that of ID 1 (2001). Hence, sum for ID 1= 7+1=8

 

My table contains millions of records. How can I calculate such sums by a Mysql script?

 

[gristoi]

firstly, try not to use sum as a field name. this is a reserved word in mysql. You can try something like this:

INSERT INTO table (Date, Value, TotalSum) VALUES ('$date', '$value', (SELECT SUM(value) FROM table WHERE Date < '$date'))

Note that i am presuming your ID is auto incimenting so i havent included it

[mikosiko]

adding a total field to the table?.... Don't do that!!!.... really bad idea... you can always calculate the sum when you get the records that you need.

[laokn]

thanks for your help.

 

i find the following one also works.

 

cheers,

 

SELECT t1.*

, COALESCE(SUM(t2.value),0) total

FROM cant_read_stickies t1

LEFT

JOIN cant_read_stickies t2

ON t2.year < t1.year

GROUP

BY id;