How to rewrite/correspond MySQL queries in Splunk?

[Username1]

Hey community I have my data in both MySQL and in Splunk. I'm trying to mimic the MySQL queries in Splunk so I can make a visual. My Data has five columns,:"Month", "Project", "Status", "Completion", "Points". The first query sums the column "Points" only if that row includes a Status and Completion value of "Done" and then grouping it by Month. The second query is summing the column points just by the Month.

The problem I'm running into is having them together in one Splunk Query as I'm trying to have both tables in one graph. Any suggestions? 

select Month,sum(Points) from TABLE
 where Status = "Done"
 and Completion = "Done"
 group by Month
 ;
&
 select Month,sum(Points)from TABLE
 group by Month
 ;

[thambisetty]

index=index

| eval done=if(Status=“Done” AND Accepted=“Done”,Points,“0”)

| stats sum(Points) as all_points, sum(done) as done_points by Month

Sample output

———-

Month all_points done_points

Jan  10 5

Feb 20 10

done will have points when status and accepted value is done. sum(done) renamed to done_points this will have sum of points where status and accepted is done by month.

hope this answers your question.

[Username1]

@thambisetty  Thanks for the response 

I ran your query and it is showing a blank for done_points

It returns the below, but done_points is all null

Month | all_points| done_points

Jan       |  10              |

Feb       |   20            |


Any thoughts on why that may be happening?

[thambisetty]

What is exact field name of Status and accepted?

Check the field names I used in eval statement are matching with field names in Splunk.

[Username1]

@thambisetty  The exact field names are Status and Accepted.
I have made sure that the field names are correct and the single quotes are correct as well.

[thambisetty]

Can you post your query here please?

and also remove last stats and run search and check if you are seeing new field done or not in the fields?

[Username1]

Here is my current query

index=index
| table Month, Project, Status, Accepted, Points
| eval done = if(Status= 'Done' AND Accepted = 'Done',Points,'0')
| stats sum(Points) as all_points, sum(done) as done_points by Month

I removed the last |stats and it shows theres no results for "done". 

[thambisetty]

Run this query as it is.

index=index

| eval done=if(Status=“Done” AND Accepted=“Done”,Points,“0”)

| stats sum(Points) as all_points, sum(done) as done_points by Month

you replaced double quotes with single quotes in eval statement. 
if eval is throwing an error for some reason just remove double quotes and input them from your keyboard.

[Username1 · Accepted solution]

index=index
| table Month, Project, Status, Accepted, Points
| eval done = if(Status= "Done" AND Accepted = "Done",Points,'0')
| stats sum(Points) as all_points, sum(done) as done_points by Month

The error was in the double and single quotes, that is my mistake...Thanks you @thambisetty  for all of your help!