CODEWITHSUNDEEP
sqlmysql
Martin
Martin

Reputation: 665

Cumulative sum in mysql view

I have a table holding values for each month of different years.

Entries:
entry_id
entry_date
entry_amount

Now I want a view which holds all entry values, and the cumulative sum of the current year's amounts.

Entries_sum_view:
entry_id
entry_date
entry_amount
entry_cumulative_yearly_sum

where entry_cumulative_yearly_sum = SUM(all entries from Jan 01 of YEAR(entry_date) up to entry_date)

Thanks, Martin

Upvotes: 2

Views: 1725

Answers (1)

Martin
Martin

Reputation: 665

Found the solution:

SELECT e1.*, SUM(e2.entry_amount) AS cum_sum 
FROM Entry e1, Entry e2
WHERE 
  e2.account_idfk = e1.account_idfk AND
  e2.entry_period_end_date BETWEEN MAKEDATE(YEAR(e1.entry_period_end_date),1) AND e1.entry_period_end_date GROUP BY e1.entry_id

Upvotes: 3

Related Questions