husui

Asking for Help on Adding Multiple COUNT(*) from Multiple Tables. (ExecuteSQL)

Discussion created by husui on Jun 18, 2018
Latest reply on Jun 18, 2018 by user19752


I have tried to follow SQL answers online on how to do this. I'm not sure if this is a FileMaker limitation, but I can't add COUNT(*) from two tables, in a GROUP BY clause. Whenever I try to use parentheses, I get syntax errors back.

 

 

Here is one table:

INPUT:

ExecuteSQL("

SELECT \"Program Name\", COUNT(*)

FROM \"Program\"

INNER JOIN \"Program Event\"

ON \"__ProgramIDpk\" = \"_ProgramIDfk\"

INNER JOIN \"Attendee Sign-In\"

ON \"_EventIDfk\" = \"__Program Event IDpk\"

WHERE \"Gender Identity\" = '" & $$Current Gender & "'

GROUP BY \"Program Name\"

"; ": "; "")

OUTPUT:

ACT III: 1

HIV Services: 2

 

Here is another table:

INPUT:

ExecuteSQL("

SELECT \"Program Name\", COUNT(*)

FROM \"Program\"

INNER JOIN \"Hall Booking\"

ON \"__ProgramIDpk\" = \"_ProgramIDfk\"

INNER JOIN \"Attendee Sign-In\"

ON \"_EventIDfk\" = \"__Hall BookingIDpk\"

WHERE \"Gender Identity\" = '" & $$Current Gender & "'

GROUP BY \"Program Name\"

"; ": "; "")

OUTPUT:

HIV Services: 2

Identi-T: 3

 

How can I achieve this?

DESIRED OUTPUT:

ACT III: 1

HIV Services: 4

Identi-T: 3

Outcomes