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
timponderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RCorfmanCommented:
I don't know where ethnicity is in the above... maybe students? I'll assume so.
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.
PluckaCommented:
timponder,

This should do it

    select      ethnicity, IsNull(Count(*), 0) as ethCount
    from        sessionRecord
    left join   students on sessionRecord.StudentID = students.StudentID

Regards
Plucka
RCorfmanCommented:
Duh. Thanks Plucka. I was thinking an outer join would solve it, but for some reason my mind went numb... or was that dumb.... ;)
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

RCorfmanCommented:
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.
PluckaCommented:
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
timponderAuthor Commented:
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

RCorfmanCommented:
No, Plucka change the query to an outer join. This will always retrieve at least one student record.  You can't do a count(*) though as that won't do the correct number of records.  You want to 'count(sessionRecord.StudentID).  Also, you need a group by when you count on one and leave another record blank.

Unfortunately, I can't set this up and run it right now as I have to leave (I'm no vacation and we are going sight-seeing). If nobody can chime in with the exact correct solution this morning and you have a deadline, I know by QofQ solution WILL work, it just isn't the most efficient by any means.  Also, we don't need an IsNull at all, count(anything) always returns a value.  It was late last night for me.

Something like.
select      ethnicity, Count(sessionRecord.StudentID) as ethCount
    from        sessionRecord
    left join   students on sessionRecord.StudentID = students.StudentID
  group by ethnicity

IF that doesn't work and noboby else responses, you can use my original solution in a pinch. I'm sorry I won't be online any more today  :(

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
timponderAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.