Display/Graph all options regardless of Count
Posted on 2006-04-18
I thought I could figure this out based on a response to a previous question, but I am obviously missing something. Due to an unexpected trip, I need to come up with something today.
I need to create some demographics tables and bar graphs from several sets of data.
I have the following query:
Count(*) as ethCount
sessionRecord.StudentID = students.StudentID
Where sessionID = #form.sessionID#
group by ethnicity
(I also have done this using a Where instead of a Join)
I get a nice result set such as:
4 African American
Using cfoutput and cfchart (in ColdFusion 6.x) I can get a nice table and corresponding bar graph.
Here is the issue:
There are five choices for ethnicity, and of course Count only picks up on the ones that are actually in the students table, so if Caucasian wouldnt happen to be listed anywhere in that session (sessionID) it is not in the reult set. They would like all the charts to be uniform - all five options listed even if the count/percentage is 0.
I thought I could make a table listing all of the choices, in this example it might be ethnicityChoices and in that table have only one column that listed all of the five options. I could then do an outer join to get a result set back that included all of the choices that were counted as well as a null or 0 (one of the two) value for the choices that were not listed in that particular query result.
I keep getting errors though, or a result set just showing the choices with an actual count. AM I off on my thinking? Is there a better way?