AnsweredAssumed Answered

ExecuteSQL for a chart - how to return 0 in a count ?

Question asked by user27086 on Sep 15, 2018
Latest reply on Sep 16, 2018 by okramis

Hi there,

 

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:

 

2017_Q1

2017_Q2

2017_Q3

2017_Q4

2018_Q1

2018_Q2

2018_Q3

 

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:

 

ExecuteSQL (

 

"SELECT count (id) from Works

WHERE status='Compliant'

GROUP BY year_quarter"

; "" ; ""  ; "")

 

This returns only 6 values, because there are 0 'Compliant' records in 2018_Q2

 

23

35

33

33

23

5

2

 

What I need is:

 

23

35

33

33

23

5

2

 

 

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.

 

Thanks much,

Steven

Outcomes