6 Replies Latest reply: Mar 29, 2018 8:15 AM by Rajasaravanan Murugesan

# group by sum

i have this kind of data

abc     a         single               213

abc     b          single               123

abc    c          single                  123

abc    d          single                  1

abc    a         multiple               123

abc    b          multiple               123

abc    c          multiple               123

abc    d          multiple               234

abc1     a         single               213

abc1     b          single               123

abc1    c          single                  123

abc1    d          single                  1

abc1    a         multiple               123

abc1    b          multiple               123

abc1    c          multiple               123

abc1    d          multiple               234

i want to get sum like this

abc     a         single               213

abc     b          single               123

abc    c          single                  123

abc    d          single                  1

sum                                         3465(whatever the sum is i just wrote random sum)

abc    a         multiple               123

abc    b          multiple               123

abc    c          multiple               123

abc    d          multiple               234

sum                                         3465(whatever the sum is i just wrote random sum)

abc1     a         single               213

abc1     b          single               123

abc1    c          single                  123

abc1    d          single                  1

sum                                         3465(whatever the sum is i just wrote random sum)

abc1    a         multiple               123

abc1    b          multiple               123

abc1    c          multiple               123

abc1    d          multiple               234

sum                                         3465(whatever the sum is i just wrote random sum)

how i get this

• ###### Re: group by sum

go for Pivot table

• ###### Re: group by sum

ok but in pivot i get data like this

 col1 col2 col3 col4 sum(col4) abc a mutliple 123 123 single 213 213 b mutliple 123 123 single 123 123 c mutliple 123 123 single 123 123 d mutliple 234 234 single 1 1 abc1 a mutliple 123 123 single 213 213 b mutliple 123 123 single 123 123 c mutliple 123 123 single 123 123 d mutliple 234 234 single 1 1

but actually i want get data like this

 single abc abcd 213 123 123 1 Sum                     457 multiple abc abcd 123 123 123 234 Sum                   603 single abc1 a 213 b 213 c 123 d 1 sum 547 multiple abc1 a 123 b 123 c 123 d 234 sum603

is this possible how to group by col4 as headers

• ###### Re: group by sum

May be like attached

• ###### Re: group by sum

Cross tables ..not sure though

• ###### Re: group by sum

Use pivot table add col1 and col 2 into Row and put sum(col4) in column area.you will get the same result.

• ###### Re: group by sum

The solution is to use TOTAL in your Sum expression. See attached application...

The expression should look like this:

Sum(TOTAL <col1,col3> col4)

This takes the sum of col4, but groups by col1 and col3. This will avoid showing the sum in terms of col2. Notice that the Totals at the top of the column is actually correct, not the sum of all the numbers below it!

Also, please note the sort order; I changed it to make the data look like your desired order.