CODEWITHSUNDEEP
mysqlsqljasper-reportsaggregate
Gunni
Gunni

Reputation: 519

How to aggregate (sum) values over month in jasperreports (each month should be the sum of all month before)

I want to do a report with jasperreports that aggregates our contracts over the month but adding all new and old contracts to the month. The database is a mysql database. My SELECT would look like this with example data below:

SELECT month(contract_date), amount
FROM contracts
WHERE year(contract_date)=2013
GROUP BY month(contract_date)

1.1.2013 300
1.1.2013 500
1.2.2013 250
1.3.2013 250

Now i get:

1 800
2 250
3 250
...

But i would like to have:

1 800
2 1050
3 1300
...

So each month contains the amount of all month before.

I dont mind if i can do this in SQL or with jasperreports/iReport, so any solution is welcome. Is there any way i can do this?

Upvotes: 0

Views: 423

Answers (1)

TommCatt
TommCatt

Reputation: 5636

MySQL doesn't have CTEs which is inconvenient, but a view will do in a pinch.

create view MonthlyTotals as
    select  Month( ContractDate ) as ContractMonth, Sum( ContractQty ) as TotalQty
    from    contracts
    group by ContractMonth;

Now we can join the view with itself, maintaining a running total of the month and all previous months:

select  t1.ContractMonth, t1.TotalQty, Sum( t2.TotalQty ) as RunningTotal
from    MonthlyTotals t1
join    MonthlyTotals t2
  on    t2.ContractMonth <= t1.ContractMonth
group by t1.ContractMonth;

The output matches your desired output, as seen at SQL Fiddle.

Upvotes: 1

Related Questions