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: Australia
, NewZealand
, Oceania
, and Other
.
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?