Grouping on multiple fields

[mikij]

 Hi,

 

I want to define a group on multiple fields, but how can I define such a group expression ? I can define group for one field, but how to define for multiple fields, like this:

 

select column1, column2, sum(column3)

from someTable

group by column1, column2

 

?

 

I need to define it in group, because I need to have variable that will sum some values on a group basis.

 

--

Thx, Miki

[kchaudhry]

You will just add two group one on column1 and the other on column2.

[mikij]

 Yes, but when I do this, it generates show all rows in a detail band, not grouped ?

E.g., if I have column1 = 1 and column2 = 1, and I group by both fields it show:

----------------------------------------------------------

column1     |      column2    |   someSum

-----------------------------------------------------------

      1                          1                  ....

      1                          1                  ....

-----------------------------------------------------------

 I want that I have just one row here, because I group by both fields and they have the same value in both rows ?

 

Thx.

[mikij]

 Actually I'm not sure should I group data in input SQL query when using iReport groups at all ? I ask this because I want to group data by some columns and to show some summary values, so I don't want to repeat the same rows (as described in post above) ?

--

Thank you in advance, Miki

[kchaudhry]

Try adding the order by clause in your query. So basically, it would be

Code:

select column1, column2, sum(column3)from someTablegroup by column1, column2order by column1, column2

[mikij]

 Thank you on answer. I'm not sure if issue is with ordering, but with showing multiple rows with all the same values. I don't know what to do, to group fields in SQL like this:

select column1, column2, sum(column3)from someTablegroup by column1, column2order by column1, column2[/code]

and in that case I cannot do some arithmetic during select, or I should group these fields in iReport, or both ? What is the difference ?

 

Thanks!