Splunk Search
Highlighted

How do I use the stats command on a field value that has duplicate entries?

Communicator

I'm trying to table sales data and would like to have my quantity field values to calculate the total number that the item_id shows in a single transaction.

Example : Let's say a customer buys 3 apples with the same itemid, 2 oranges w/ the same itemid and one banana. I would like to know how to blend a stats for Quantity of each itemid in the transaction so they don't show all three apples in the transaction on their own line in my stats list or values(itemid).

![index=businesstrans  customer=*
| stats list(item_id) as item_id, list(description) as Description, list(quantity) as quantity, list(unit_price) as unit_price, sum(unit_price) as Transaction_Total by customer, _date
| table _date, customer, item_id, Description, quantity, unit_price, Transaction_Total
| sort - Transaction_Total][1]
Highlighted

Re: How do I use the stats command on a field value that has duplicate entries?

SplunkTrust
SplunkTrust

@johnward4

Is it ok if you display all transactions and Total? Can you please try this?

index=businesstrans  customer=* 
| eventstats sum(unit_price) as Transaction_Total by customer, _date
| table _date, customer, item_id, Description, quantity, unit_price, Transaction_Total
| sort - Transaction_Total
0 Karma
Highlighted

Re: How do I use the stats command on a field value that has duplicate entries?

Motivator

Hi @johnward4 ,
Please try below query,

![index=businesstrans  customer=*
| stats first(description) as description, sum(quantity) as quantity, first(unit_price) as unit_price by customer, _date, item_id
| stats list(item_id) as item_id, list(description) as Description, list(quantity) as quantity, list(unit_price) as unit_price, sum(eval(unit_price*quantity)) as Transaction_Total by customer, _date 
| table _date, customer, item_id, Description, quantity, unit_price, Transaction_Total
| sort - Transaction_Total][1]

Hope this helps!

View solution in original post

0 Karma
Highlighted

Re: How do I use the stats command on a field value that has duplicate entries?

Communicator

Thanks @VatsalJagani ! This query works for everything except it doesn't like the stats sum(unitprice*quantity) as TransactionTotal by _date, customer

I tried to run an eval under the stats

eval TransactionTotal = unitprice * quantity

and that gives me the calculation total of say for example quantity was 3 items it multiply by the unit price for a single item and returns the sum in TransactionTotal but it is showing line by line and I need to group everything as a single transaction with the transactiontotal showing the sum of all of their purchases

0 Karma
Highlighted

Re: How do I use the stats command on a field value that has duplicate entries?

Influencer

Hi @johnward4,

It is
stats sum(eval(unitprice*quantity)) and Not stats sum(unitprice*quantity), change and see should work fine.

Highlighted

Re: How do I use the stats command on a field value that has duplicate entries?

Communicator

Thank you @Vijeta and @VatsalJagani !! I didn't even know that you could run an eval in a stats command like, that will be def useful to me. Last question if you don't mind..

I'm using the query as a drilldown from my main bar graph panel displaying total purchases by customer and passing in the value.name2 (selectCustomer) to this drilldown panel. My overall goal would be able to group the transactions by customer, date AND most important broken out by rtlloc_id aka store

index=sales cust_party_id=$selectCustomer$
| stats first(rtl_loc_id) as rtl_loc_id, first(description) as description, sum(quantity) as quantity, first(unit_price) as unit_price by cust_party_id, business_date, item_id
| eval unit_price = round(unit_price, 2)
 | stats list(rtl_loc_id) as Store_ID, list(item_id) as item_id, list(description) as Description, list(quantity) as quantity, list(unit_price) as unit_price, sum(eval(unit_price*quantity)) as Transaction_Total by cust_party_id, business_date 
 | table _date, cust_party_id, Store_ID, item_id, Description, quantity, unit_price, Transaction_Total
 | sort - quantity
| rename unit_price as Sales_Price
0 Karma
Highlighted

Re: How do I use the stats command on a field value that has duplicate entries?

Influencer

From what I understand is you want the transactions broken by customerid, Date and Store. You can use rtlloc_id in your by clause instead of list.

| stats list(itemid) as itemid, list(description) as Description, list(quantity) as quantity, list(unitprice) as unitprice, sum(eval(unitprice*quantity)) as TransactionTotal by custpartyid, rtllocid ,businessdate | rename rtllocid are StoreID , unitprice as SalesPrice | sort - quantity