Create Plotalot chart from RSForm Pro data using pivot query

Question

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

Answer 1

It looks like you found the pivot pages on JSE -- well done. Also, you coding attempt is very close -- you just need to create two-column rows for PlotALot.

You didn't supply me with realistic database data, so I needed to mock up my own minimal sample data set.

INSERT INTO `r4l7i_rsform_submission_values` (`SubmissionValueId`, `FormId`, `SubmissionId`, `FieldName`, `FieldValue`) VALUES
(101, 4, 1, 'resolvido', 'Não'),
(102, 4, 1, 'EXITDATE', ''),
(103, 4, 2, 'resolvido', 'Sim'),
(104, 4, 2, 'EXITDATE', ''),
(105, 4, 3, 'resolvido', 'Não'),
(106, 4, 3, 'EXITDATE', ''),
(107, 4, 4, 'resolvido', 'Não'),
(108, 4, 4, 'EXITDATE', '2021-06-01'),
(109, 4, 5, 'resolvido', 'Sim'),
(110, 4, 5, 'EXITDATE', ''),
(111, 4, 6, 'resolvido', 'Não'),
(112, 4, 6, 'EXITDATE', '');

This has 4 Não values (relating to SubmissionIds: 1,3,4,6) and 2 Sim values (relating to SubmissionIds 2,5). SubmissionId 4 is logically disqualified because it has a non-empty EXITDATE value.

SQL: (https://www.db-fiddle.com/f/dfRoMAGLpa4qfe22rHffS6/1)

SELECT Resolvido,
       COUNT(1) AS Count
FROM (
    SELECT
        MAX(IF(FieldName = 'resolvido', FieldValue, NULL)) AS `Resolvido`
    FROM r4l7i_rsform_submission_values
    WHERE FormId = 4
    GROUP BY SubmissionId
    HAVING MAX(IF(FieldName = 'EXITDATE', FieldValue, '')) = ''
) DerivedTable
GROUP BY Resolvido

Result Set:

| Resolvido | Count |
| --------- | ----- |
| Não       | 3     |
| Sim       | 2     |

Rendered Pie Chart (with and without hover effect):