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

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

johnward4 — Tue, 29 Sep 2020 21:24:24 GMT

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 item_id, 2 oranges w/ the same item_id and one banana. I would like to know how to blend a stats for Quantity of each item_id in the transaction so they don't show all three apples in the transaction on their own line in my stats list or values(item_id).

![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]

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

kamlesh_vaghela — Fri, 28 Sep 2018 04:32:52 GMT

@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

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

VatsalJagani — Fri, 28 Sep 2018 05:26:41 GMT

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!

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

johnward4 — Tue, 29 Sep 2020 21:24:35 GMT

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

I tried to run an eval under the stats

eval Transaction_Total = unit_price * 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 Transaction_Total but it is showing line by line and I need to group everything as a single transaction with the transaction_total showing the sum of all of their purchases

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

Vijeta — Tue, 29 Sep 2020 21:24:38 GMT

Hi @johnward4,

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

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

johnward4 — Tue, 29 Sep 2020 21:24:41 GMT

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 rtl_loc_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

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

Vijeta — Tue, 29 Sep 2020 21:24:44 GMT

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

| 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 cust_party_id, rtl_loc_id ,business_date | rename rtl_loc_id are Store_ID , unit_price as Sales_Price | sort - quantity