group by sum

[capriconuser]

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

[deepali_more]

go for Pivot table

[capriconuser]

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

[sasiparupudi1]

May be like attached

[deepali_more]

Cross tables ..not sure though

[rajasaravananm]

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

[Anonymous]

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.