1

So I got this Pie Chart to display the Count of Voters vs Position and it's working fine.

SELECT Position,
COUNT(1) AS count
FROM (
    SELECT
      MAX(IF(FieldName = 'Position', FieldValue, NULL)) AS `Position`
    FROM syh4g_rsform_submission_values
    WHERE FormId = 1
    GROUP BY SubmissionId
) DerivedTable
GROUP BY Position

Now, I have another field called Part No which is basically a dropdown field of numbers starting from 1 to 47 in the same form.

I want to create a Pie Chart for Part No: 5 only showcasing the Count of Position against the Name.

How do I go about it?

EDIT: Here's a sample of my DB Table. Please note that the sample data has Part No 5: https://www.db-fiddle.com/f/eeYuhZSefJ2XsV4Jpmn6Hv/0

5
  • 1
    Thanks for your question, Elaine, please take our tour. It looks like you have crafted a good question AND in my opinion, you have tagged your question perfectly. This indicates that you know you are after a pivot query. The "DerivedTable" hints that you may have toiled with some of my guidance found elsewhere on JSE. These are all great indicators of your effort. Could you please create a db-fiddle with a sufficient amount of sample data and your desired output? It's always good to see people with Stack Exchange experience coming to JSE! Nov 30, 2021 at 22:24
  • 1
    I'm not quite sure if you are asking for another condition inside of your MAX() or if you want a HAVING clause. For instance, will HAVING MAX(IF(FieldName = 'Part No', FieldValue, NULL)) = '5' work if you write it after GROUP BY SubmissionId? I might be able to perform the same logic a couple of other ways if you can provide a db sandbox for me. Nov 30, 2021 at 22:33
  • @mickmackusa Thanks for your kind words. Currently, the piechart shows for all data. However, I want the piechart only for those data where the Part No is 5. Dec 1, 2021 at 4:00
  • 1
    Did you try my HAVING suggestion in my second comment? Dec 1, 2021 at 4:01
  • 1
    @mickmackusa HOT DAMN!! Looks like it is working when I added that line of code after GROUP BY SubmissionId. Could you post as an answer so I can accept it? Dec 1, 2021 at 4:01

1 Answer 1

1

Add a HAVING clause that pre-qualifies aggregrate data in your derived table. The following condition will require that a submission contains a Part No value of 5.

HAVING COUNT(FieldName = 'Part No' AND FieldValue = '5') 

Full SQL: (DB Fiddle)

SELECT Position,
       COUNT(1) AS Count
FROM (
    SELECT MAX(IF(FieldName = 'Position', FieldValue, NULL)) AS `Position`
    FROM syh4g_rsform_submission_values
    WHERE FormId = 1
    GROUP BY SubmissionId
    HAVING COUNT(FieldName = 'Part No' AND FieldValue = '5')
) DerivedTable
GROUP BY Position

Less elegantly, it could be:

HAVING MAX(IF(FieldName = 'Part No', FieldValue, NULL)) = '5'

or

HAVING MAX(IF(FieldName = 'Part No' AND FieldValue = '5', 'keep', NULL)) = 'keep'

or

HAVING '5' IN (MAX(IF(FieldName = 'Part No', FieldValue, NULL)))

or

HAVING FIND_IN_SET('5', GROUP_CONCAT(IF(FieldName = 'Part No', FieldValue, NULL)))
1
  • 1
    Thank you so much!! This works perfectly. Dec 1, 2021 at 4:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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