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

    group by sum

    Capricon User

      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
          deepali m

          go for Pivot table

            • Re: group by sum
              Capricon User

              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

              a

              b

              c

              d

              213

              123

              123

              1

              Sum                     457

                    multiple

              abc

              a

              b

              c

              d

              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
              Rajasaravanan Murugesan

              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
                Mitch Boyer

                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.

                 

                Capture.JPG