How to group rows together in a table and calculate the sum for each group?

[asaste]

Hi,

I need to create table as shown in this screenshot:

I have written this search for that:

index=em7_srm_summary_data srm_units_used>0 | dedup ticket_id | sort cust_name, ticket_id | eval "Closed Date" = if (ticket_date_closed!=0, strftime(ticket_date_closed,"%m/%d/%Y"), "") | stats list("ticket_id") as "Ticket Id", list("Closed Date") as "Closed Date" ,list("srm_units_used") as "SR Units Used" by cust_name | addcoltotals "SR Units Used" by cust_name

I need "SRU Units Used" count for each Customer, but I am not able to get the column total for each Customer using above search. Any idea how to do that?

[sundareshr]

Try this

index=em7_srm_summary_data srm_units_used>0 | dedup ticket_id | sort cust_name, ticket_id | eval "Closed Date" = if (ticket_date_closed!=0, strftime(ticket_date_closed,"%m/%d/%Y"), "") | stats list("ticket_id") as "Ticket Id", list("Closed Date") as "Closed Date" ,list("srm_units_used") as "SR Units Used" by cust_name | appendpipe [|stats sum(srm_units_used) as srm by cust_name | eval cust_name=cust_name." Total"] | sort cust_name

[asaste]

Thanks a lot for your quick reply.
Yes , your query is giving column total for each Customer. But I am not getting column total exactly below customer name. i am getting it in following order.

Abhi Corp
Abhi Corp MSP
*Abhi Corp MSP Total*
*Abhi Corp Total*
Aniket Corp
*Aniket Corp Total*

Also query is adding column header as 'srm'. Is it possible to have sum in same "SR Units Used" column ?

[somesoni2]

Little variation of above to give you proper order

index=em7_srm_summary_data srm_units_used>0 | dedup ticket_id | sort cust_name, ticket_id | eval "Closed Date" = if (ticket_date_closed!=0, strftime(ticket_date_closed,"%m/%d/%Y"), "") | stats list("ticket_id") as "Ticket Id", list("Closed Date") as "Closed Date" ,list("srm_units_used") as "SR Units Used" by cust_name | streamstats count as serialno | appendpipe [|stats max(serialno) sum(srm_units_used) as "SR Units Used" by cust_name | eval cust_name=cust_name." Total"] | sort serialno | fields - serialno

[asaste]

Thanks somesoni2.
I have tried your query but it is providing following order.
Abhi Corp
Abhi Corp MSP
Aniket Corp
Abhi Corp Total
Abhi Corp MSP Total
Aniket Corp Total

I need following order
Abhi Corp
Abhi Corp Total
Abhi Corp MSP
Abhi Corp MSP Total
Aniket Corp
Aniket Corp Total

Also Total it is giving as 1,2,3 instead of 35, 21, 50 as below

Customer_Name max(serialno)

Abhi Corp Total 1
Abhi Corp MSP Total 2
Aniket Corp Total 3

I am currently modyfying query you have provided to get correct Sum of "SR Units Used"

Thanks,
Abhi

[harish1992]

Have you got any answer for this question? Did you get your table results in the above mentioned format?

[sundareshr]

You can change the column name to be whatever you would like to be by changing the as clause. Like so...

.... | appendpipe [|stats sum(srm_units_used) as "SR Units Used" by cust_name  ] | sort cust_name | eval cust_name=if(isnull("Ticket Id"), cust_name." Total", cust_name))