Using sql CASE operator with cfchart
Today, I had to create charts from percentage values stored as integers in a database. The idea was to create a bar or pie chart that would show the number of results in different ranges of percentages. The data was collected through a text input that allowed integer values between 0 and 100. I used the sql CASE operator select counts of the number of responses in each desired range. Here is an example of the sql used.
SELECT
COUNT(CASE WHEN resultPercent = 0 THEN resultPercent END) as 'Range0',
COUNT(CASE WHEN resultPercent BETWEEN 1 AND 15 THEN resultPercent END) as 'Range1',
COUNT(CASE WHEN resultPercent BETWEEN 16 AND 30 THEN resultPercent END) as 'Range2',
COUNT(CASE WHEN resultPercent BETWEEN 31 AND 45 THEN resultPercent END) as 'Range3',
COUNT(CASE WHEN resultPercent BETWEEN 46 AND 60 THEN resultPercent END) as 'Range4',
COUNT(CASE WHEN resultPercent BETWEEN 61 AND 75 THEN resultPercent END) as 'Range5',
COUNT(CASE WHEN resultPercent BETWEEN 76 AND 90 THEN resultPercent END) as 'Range6',
COUNT(CASE WHEN resultPercent BETWEEN 91 AND 100 THEN resultPercent END) as 'Range7'
FROM testresults
</cfquery>
Now, cfchart is used to create a chart of the counts from the query results. Here is an example of the chart code.
<cfchartseries type="bar">
<cfchartdata item="0%" value="#testresults.Range0#">
<cfchartdata item="1 - 15%" value="#testresults.Range1#">
<cfchartdata item="16 - 30%" value="#testresults.Range2#">
<cfchartdata item="31 - 45%" value="#testresults.Range3#">
<cfchartdata item="46 - 60%" value="#testresults.Range4#">
<cfchartdata item="61 - 75%" value="#testresults.Range5#">
<cfchartdata item="76 - 90%" value="#testresults.Range6#">
<cfchartdata item="90 - 100%" value="#testresults.Range7#">
</cfchartseries>
</cfchart>
You can use these CASE statements to show ranges of different values with the cfchart tag. As always there is different ways to solve the same problem. I'm curious if others have a way of accomplishing the same results in a different way?
Comments
@Andy - Thanks for the great tips in your comment. I was not familiar with the COALESCE function so I needed to check it out. After reading your blog post on the same topic from Sept 29th of last summer just now, it makes sense. Thanks for helping me to learn something new! That is what makes these blogs great.
I solved something similar by creating a bucket, or range, table that essentially held my BETWEEN min and max values. Then I query the range table and while looping through it I COUNT the underlying transactional table data for records that fall between the range table record's min and max values. That way my solution is not hard coded as all I have to do is change data values in the range table to change the way my program behaves. I can also make it more scalable by adding/removing records - which effects the number of buckets that I have. You should also use the COALESCE SQL function to replace any NULL query results because they'll blow up your cfchart if you don't turn them into zeros first.