1

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

enter image description here

1
0

It looks like you found the 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):

enter image description here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.