Splunk Search

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

johnward4
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 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]
1 Solution

VatsalJagani
SplunkTrust
SplunkTrust

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

VatsalJagani
SplunkTrust
SplunkTrust

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!

0 Karma

johnward4
Communicator

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

0 Karma

Vijeta
Influencer

Hi @johnward4,

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

johnward4
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 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
0 Karma

Vijeta
Influencer

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

kamlesh_vaghela
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...