Aggregate values "column based"

tomdoe

Hello,

I have a problem while creating a report.

I am using BIRT 2.3 with a mysql database.

My table looks like

"grouping","x","y"
1,-0.004253063,-0.002339738
2,0.006725857,0.002955924
3,-0.003102467,0.007429517
1,-0.006085681,-0.006151364
2,0.01217442,0.001887013
3,-0.006356443,0.01047763
1,-0.0114442,0.01186888
2,0.01169455,0.002339335
3,0.002002897,0.02559207
1,-0.008326302,0.009104015
2,0.006026436,0.00144702
3,0.003647684,0.01689492

Now I need to build the difference from the x values where grouping is 3 and 2 and build the average, standard deviation, minimun and maximum from that.

It means I have -0.003102467, -0.006356443 and so on for x where grouping is 3 and 0.006725857, 0.01217442 and so on where grouping is 2.
Then I have to aggregate the result to avg, std, min and max.

The problem is, that the values looks like "row based", but what I need is a "column based" result.

How could I realize this in help with the BIRT engine?


Thanks,
Tom

bgbaird

I think that what you're looking for is something like this:

Group AVG STD MIN MAX
1 a1 s1 mi1 mx1
2 a2 s2 mi2 mx2
3 a3 s3 mi3 mx3

If that is the case, you would group the table on you group, then put in your aggregations on the group.