newbie question: how to group by several columns, have different total functions

[wcaples]

Okay, all I want to do is have a report that behaves like an SQL group by statement.
data like this:
first last state sales commission
joe  fox  tx      10    4
joe  fox  tx      20    5
sue  ton  tn     15    7
sue  ton  tn     25    9

I want to see select first, last, state, sum(sales), avg(commission) group by first,last,state:
first last state sum(sales) avg(commission)
joe  fox  tx     30              4.5
sue ton  tn     40              8

that's it!  can't figure out how to do it.  If I group by more than one column, then add different types of summary, it blows up into a different line for each group and type of summary.

[MFGF]

Okay, all I want to do is have a report that behaves like an SQL group by statement.
data like this:
first last state sales commission
joe  fox  tx      10    4
joe  fox  tx      20    5
sue  ton  tn     15    7
sue  ton  tn     25    9

I want to see select first, last, state, sum(sales), avg(commission) group by first,last,state:
first last state sum(sales) avg(commission)
joe  fox  tx     30              4.5
sue ton  tn     40              8

that's it!  can't figure out how to do it.  If I group by more than one column, then add different types of summary, it blows up into a different line for each group and type of summary.

Hi,

You shouldn't need to do any manual grouping in your report to get this result. Reports will automatically group and summarize - or at least they should. All you'd need to do would be to ensure the aggregation for Sales is set to Total and for Commission is set to Average.

If your report isn't behaving like this, there must be something odd going on. Can you describe how you authored the report?

MF.