How do you represent SQL case and sum/count in a search query?

[tommyp1972]

I am using the Splunk web service to get data that was previously gotten from a database table that stored log information. I have no idea how t hey got the info into the databases. However, I am trying to replicate it.

I cannot find a way to get the sum and count when the "fault" field" is "N" or "y". Can anyone help? Here is the SQL I am trying to translate:

select
name,
sum(case when fault = 'N' then 1 else 0 end) as pass,
sum(case when fault = 'Y' then 1 else 0 end) as fail,
count(*) total,
. . . . .

[tommyp1972]

I don't know. But I will surely try it and post my findings here. THanks for the reply.

[tommyp1972]

OK. I checked. That didn't work. The count worked for the case part (pass and fail) didn't work. I swear I searched the documentation and until just now I found the eval/where and there is a case. The case I found yesterday was for case insensitivity/sensitivity for strings. Anyway, I tried this but I get 0's for the pass and fail but I know that is wrong as the total has a number greater than 0.

count(eval Pass=case(fault==Y,1)), count(eval Fail=case(fault==N,1)),

[Ayn]

I find that it's often easier to state the GOAL rather than how you would achieve it in some other language. While Splunk's search language can sometimes be similar to how SQL handles things it's often harder to make direct translations between the two than independently coming up with a solution in either language to the problem.

Is this something like what you're looking for?

... | stats count(eval(fault=="N")) as pass, count(eval(fault=="Y")) as fail, count as total