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
  • 252 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by pratik_garg

Dani AI

Generated

Brief summary and an efficient path forward.

The answers from and (self-join and correlated-subquery) are functionally correct for the small sample, but both patterns can become prohibitively slow on millions of rows because they do repeated work (effectively O(n^2) in the worst case). A practical improvement is to (a) aggregate at the date level first, because the requirement is "sum of values with date < this record's date" (not row-by-row ordering), and then (b) compute cumulative totals across dates. That reduces the problem size to the number of distinct dates and eliminates per-row nested scans.

If you run MySQL 8.0+, use window functions. The sequence below aggregates per-date then produces, for each date, the sum of all earlier dates (strictly less than):

WITH date_totals AS (
  SELECT `date`, SUM(value) AS date_total
  FROM mytable
  GROUP BY `date`
),
date_cum AS (
  SELECT `date`,
         COALESCE(SUM(date_total) OVER (ORDER BY `date`) - date_total, 0) AS sum_before
  FROM date_totals
)
SELECT t.id, t.`date`, t.value, dc.sum_before AS sum
FROM mytable t
LEFT JOIN date_cum dc ON t.`date` = dc.`date`;

If you are on pre-8.0 MySQL, do the same aggregation and then join the aggregated-date table against itself (much cheaper than row-level self-join because it works on distinct dates, not every row):

SELECT t.id, t.`date`, t.value,
       COALESCE(SUM(d2.date_total),0) AS sum_before
FROM mytable t
JOIN (SELECT `date`, SUM(value) AS date_total FROM mytable GROUP BY `date`) d1
  ON t.`date` = d1.`date`
LEFT JOIN (SELECT `date`, SUM(value) AS date_total FROM mytable GROUP BY `date`) d2
  ON d2.`date` < d1.`date`
GROUP BY t.id, t.`date`, t.value;

Practical tips: add an index on the date column; if date has a time portion and you mean calendar-day comparisons, normalize with DATE(date_col); use EXPLAIN to check plans; for very large, mostly-static datasets precompute and store per-date cumulative totals (materialized table) and join that—much faster for repeated reads. Also be careful not to rely on MySQL's nonstandard grouping behavior: list non-aggregated columns in GROUP BY (or use ANY_VALUE) to avoid ambiguous results.

Recommended Answers

All 4 Replies

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.