In the backend, I am compiling some survey results generated from RSForm Pro and I want to display the statistics graphically using a PlotALot pie chart.
My form has 4 possible values for this particular question:
I started with the following query in the sql textarea of the Plot1 tab:
SELECT FieldValue, COUNT(1) FROM jprefix_rsform_submission_values WHERE FormId = 8 AND FieldName = 'Location' GROUP BY 1 ORDER BY 2 DESC
However, this omits any location that has a count of zero. To ensure that all possible locations were included, I derived a table of default locations via a subquery, then joined the RSForm data to the derived table.
SELECT Location, COUNT(FieldValue) FROM ( (SELECT 'Australia' AS Location) UNION (SELECT 'New Zealand') UNION (SELECT 'Oceania') UNION (SELECT 'Other')) AS defaults LEFT JOIN jprefix_rsform_submission_values ON FormId = 8 AND FieldName = 'Location' AND FieldValue = REPLACE(Location, ' ', '') GROUP BY 1 ORDER BY 2 DESC
This now provides the correct result set:
Location | COUNT(FieldValue) -------------------------------- Australia | 46 Other | 7 New Zealand | 0 Oceania | 0
The trouble is, when I feed this sql to PlotAlot, I only see:
How can I prevent PlotAlot from omitting the zero values in the legend?