timponder
asked on
Display/Graph all options regardless of Count
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:
==
SELECT ethnicity,
Count(*) as ethCount
FROM
sessionRecord
INNER JOIN
students ON
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:
EthCount Ethnicity
4 African American
7 Asian
11 Caucasian
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?
thanks!
tim
I need to create some demographics tables and bar graphs from several sets of data.
I have the following query:
==
SELECT ethnicity,
Count(*) as ethCount
FROM
sessionRecord
INNER JOIN
students ON
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:
EthCount Ethnicity
4 African American
7 Asian
11 Caucasian
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?
thanks!
tim
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Duh. Thanks Plucka. I was thinking an outer join would solve it, but for some reason my mind went numb... or was that dumb.... ;)
Does depend on the database though. Not all databases support IsNull. For instance, if it is Oracle (which is what I have in my prod environment), it would be nvl instead of isnull, but your solution is correct... in my opinion.
Yep,
And depending on where ethnicity lives, I don't know which table, you might need to reverse it as such
select ethnicity, IsNull(Count(*), 0) as ethCount
from students
left join sessionRecord on sessionRecord.StudentID = students.StudentID
Regards
Plucka
And depending on where ethnicity lives, I don't know which table, you might need to reverse it as such
select ethnicity, IsNull(Count(*), 0) as ethCount
from students
left join sessionRecord on sessionRecord.StudentID = students.StudentID
Regards
Plucka
ASKER
Hi -
Thank you.
The database is MS Access 2000, and you are correct - ethnicity lives in students.
When I try
select ethnicity, IsNull(Count(*), 0) as ethCount
from sessionRecord
left join students on sessionRecord.StudentID = students.StudentID
I get an error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Wrong number of arguments used with function in query expression 'IsNull(Count(*), 0)'.
My brain has been (d)numb, and I dont understand where this will pick up the 0 entries. I think I explained it incorrectly. Ethnicity is a single column in the students table. What is written into it is the ethnicity chosen from a form, if I choose Asian, Asian is the entry in the database. SO if no one in that session chose Asian, it does not show up in my queries. (DOesnt count what it does not see)
Maybe I need to do the database and/or form differntly?
thank you,
tim
Thank you.
The database is MS Access 2000, and you are correct - ethnicity lives in students.
When I try
select ethnicity, IsNull(Count(*), 0) as ethCount
from sessionRecord
left join students on sessionRecord.StudentID = students.StudentID
I get an error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Wrong number of arguments used with function in query expression 'IsNull(Count(*), 0)'.
My brain has been (d)numb, and I dont understand where this will pick up the 0 entries. I think I explained it incorrectly. Ethnicity is a single column in the students table. What is written into it is the ethnicity chosen from a form, if I choose Asian, Asian is the entry in the database. SO if no one in that session chose Asian, it does not show up in my queries. (DOesnt count what it does not see)
Maybe I need to do the database and/or form differntly?
thank you,
tim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
I will be out all day and I will give this a shot tonight!
I also can do the Q ofQ now and it should be fine (small amount of data) then as I learn as well, I can (hopefully) make it more efficient!
Enjoy your vacation!
tim
I will be out all day and I will give this a shot tonight!
I also can do the Q ofQ now and it should be fine (small amount of data) then as I learn as well, I can (hopefully) make it more efficient!
Enjoy your vacation!
tim
I would do this using a second query to get the distinct students, then using a Query of Queries (QofQ) to combine the two.
Add a second query.
select distinct ethnicity,0 as ethCount
from student
I'll assume your first query was named Q1
and this second is named Q2.
No, you create a new query that is a QofQ that combines the two queries.
<cfquery name="qryCombineData" dbtype="query">
select ethnicity, ethCount from Q1
union all
select ethnicity, ethCount from Q2
</cfquery>
and then create a final QofQ query that you can use for the chart.
<cfquery name="qryChartData" dbtype="query">
select ethnicity,sum(ethCount) as ethCount
from qryCombineData
group by ethnicity
</cfquery>
This will give you a final query for charting that has all the ethnicity records that were in the students table with a count as appropriate.... 0 if there were none in the original query, or the count from the original query if they were included there already.