Member Avatar for 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?

  • 3 Contributors
  • 4 Replies
  • 210 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by pratik_garg
Member Avatar for urtrivedi

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
Member Avatar for pratik_garg

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
Member Avatar for laokn

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;

Member Avatar for pratik_garg

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 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.