0

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?

3
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by pratik_garg
0

This query is assuming that your table will have only one record for each year, that is
for 2001 there is only 1 record in your table and like that for every year.

SELECT a.id, a.date, a.value, SUM( b.value ) prev
FROM  `tablename` a
LEFT OUTER JOIN prevsum b ON a.date > b.date
GROUP BY a.id, a.date, a.value
0

hi laokn,
you can extract this information appling following query also..

SELECT a.id,
       a.DATE, 
       a.value,
      (select SUM(b.value) from 'tablename' b where a.DATE > b.DATE)  prev
FROM  `tablename` a
0

thanks urtrivedi and pratik_garg for your helps.

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;

0

i find the following one also works. ...

this solution is looking like solution given by urtrivedi.

one more thing you forgot something in you last post..

GROUP
BY id;

should give error...
you have to use table alise name with this field also

like t1.id.


this solution what you have pasted will give n-1 record (will not give record for year which is minimum in you table)

urtrivedi has maintain this in his solution as appling LEFT OUTER JOIN.

you can take that solution this will work very fine....

Edited by pratik_garg: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.