Aggregating a resultset in java

Question

I have a sales database table with the following data:

Year   Month  Flowertype   Totalsales
2014   1           dandalion       100
2014   2           dandalion       100
2014   2           rose               100
2014   3           rose               100
2015   1           dandalion       100
2015   2           dandalion       100

And i would like to add aggregations of the totalsales data based on the other columns, like this:

Year   Month  Flowertype   Totalsales
2014   1           dandalion       100
2014   2           dandalion       100
2014   2           rose               100
2014   3           rose               100
2015   1           dandalion       100
2015   2           dandalion       100

---

all       1           dandalion       200
all       2           dandalion       200
all       2           rose               200
all       3           rose               100
2014   all          dandalion      200
2014   all         rose               200
2015   all         dandalion       200
2014   1           all                  100
2014   2           all                  200
2014   3           all                  100
2015   1           all                  100
2015   2           all                  100
all       all         dandalion       400
all       all          rose              200
2014   all         all                  400
2015   all         all                  200
all       1           all                  200
all       2           all                  300
all       3           all                  100
all     all           all                  600

Can someone push me in the right direction on, after connecting to the database, how i should do that? I would like to do it in Java. I also would like to do it in an efficient way, because the real table contains many more columns/records.

To clarify: i would like to write a smart script that inserts the aggregated data. And i would like to hear some idea's on how that script should work. Thanks!

Answer 1

I recommend to create views for each of the particular aggregations and then just issue a simple SELECT on those. For instance:

all       1           dandalion       200
all       2           dandalion       200
all       2           rose            200
all       3           rose            100 

could be

SELECT 'all' as year, month, flowertype, sum(totalsales) from sales group by month, flowertype order by month

and the next block

2014   all         dandalion       200
2014   all         rose            200
2015   all         dandalion       200 

could be

SELECT year, 'all' as month', flowertype, sum(totalsales) from sales group by year, flowertype order by year

etc.