I'm trying to extract RSForm Pro form data from the database to create Plotalot charts.
There is a field on the form called "resolved" to which users respond "Yes or No". I would like to add up all "Yes" and all "No" and add up the amount of "Yes" and "No" separately to generate a Pizza chart. However, when I enter the code below I get an error and it doesn't generate the graph:
Plot error: Plot 1: Unknown column 'Yes' in 'field list'
The code I'm trying is:
SELECT FieldName, SUM(Sim) AS Sim, SUM(Não) AS Não FROM ( SELECT MAX(IF(FieldName = 'FIELDNAME', FieldValue, NULL)) AS FieldName, MAX(IF(FieldName = 'FIELDVALUE' AND FieldValue = 'Não', 1, 0)) AS Nãoo, MAX(IF(FieldName = 'FIELDVALUE' AND FieldValue = 'Sim', 1, 0)) AS Simm FROM r4l7i_rsform_submission_values WHERE FormId = 4 GROUP BY SubmissionId HAVING MAX(IF(FieldName = 'EXITDATE', FieldValue, NULL)) = '' ) DerivedTable GROUP BY FieldName ORDER BY FieldName, Sim, Não