where clause with a variable

[SanthoshSreshta]

Hi.

I need to get sum of total_revenue where churn=1.
I am able to get the count of churn whose churn=1 and total count of chur but not for other variable total_revenue.
Can any one help me out to achieve this

I used the query to get count churn=1 :
sourcetype="Customer_Churn"

| eval CHURN = if(CHURN="1",1,0)
| stats sum(CHURN) as Churned_Customers,count(CHURN) as Total_Churn by PLACEMENT
Thanks,
Santhosh.

[vganjare · Accepted solution]

change stats to eventstats. Splunk has where command @ http://docs.splunk.com/Documentation/Splunk/6.2.2/SearchReference/Where

[SanthoshSreshta]

Thank you @vganjare
sourcetype="Customer_Churn"

| eventstats sum(Total_Revenue) as ff by PLACEMENT
| where CHURN="1"
| stats sum(Total_Revenue) by PLACEMENT

[SanthoshSreshta]

@vganjare, Can we able to get sum(total_revenue) without any condition. in the same query.

[vganjare]

using eventstats, all the events are updated with new fields i.e. if you use eventstats sum(Total_Revenue) , then all the events will have same value for sum field. More @ http://docs.splunk.com/Documentation/Splunk/6.2.2/SearchReference/Eventstats

[vganjare]

Hi,

Not able to understand the question. Can you please elaborate?

Thanks,
Vishal

[SanthoshSreshta]

I need the sum(total_revenue) where churn=1.
I have two variable total revenue and churn. churn has two values 1 and 0.
like in SQL: select sum(total_variable) from my_source_table where churn=1