AnsweredAssumed Answered

ExecuteSQL WHERE multiple criteria error

Question asked by teclo on Aug 21, 2013
Latest reply on Aug 26, 2013 by DanielYates

Title

ExecuteSQL WHERE multiple criteria error

Post

     Here we go again. I am trying to create a stacked chart using ExecuteSQL that shows the number of members in different groups, divided by gender. This is the statement I use for the male X-axis' series:

     ExecuteSQL(
     "
     SELECT COUNT(*)
     FROM \"Table\"
     AND \"Gender\" = ?
     GROUP BY \"Group\"
     ORDER BY \"Group\"
     "
     ;"";¶; "Male" )

     And this works just fine. However, I also have to remove the inactive members from the chart. I thought I could do this by just adding an AND to the WHERE clause like this:

     ExecuteSQL(
     "
     SELECT COUNT(*)
     FROM \"Table\"
     WHERE \"Active\" = ?
     AND \"Gender\" = ?
     GROUP BY \"Group\"
     ORDER BY \"Group\"
     "
     ;"";¶; 1; "Male" )

      

     This does in fact remove a number of members that corresponds to the number of inactive men, but the results are displayed on the wrong groups. So a group that has 4 male members is displayed in the chart as having none and vice versa. I couldn't find much information about this related to FileMaker, and the solutions I tried that I found on SQL forums and sites didn't work. Any ideas? Thanks in advance!

Outcomes