I have a single table, with fields that includes: status, year_quarter
I'm trying to write two ExecuteSQL queries that will generate a list of values to use in a chart.
I'm able to generate the first that I need, which are meant to be the axis labels:
So, 7 quarters of data are in the table. So far so good.
The second query needs to generate values based on a count where status = a certain value.
So, for example:
"SELECT count (id) from Works
GROUP BY year_quarter"
; "" ; "" ; "")
This returns only 6 values, because there are 0 'Compliant' records in 2018_Q2
What I need is:
I've been all over the internet and it seems I may have to do something with a left join or nested query here.. but my attempts at this have failed for one reason or another. Can FileMaker LEFT JOIN to the same table a la a FileMaker self join??? In any case, if anyone could help out with this I'd be very happy.