Author Topic: newbie question: how to group by several columns, have different total functions  (Read 242 times)

Offline wcaples

  • Associate
  • **
  • Join Date: May 2019
  • Posts: 1
  • Forum Citizenship: +0/-0
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.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,726
  • Forum Citizenship: +640/-10
  • Cognos Software Muppet
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.
Meep!

 



       
Twittear