?
Solved

Display/Graph all options regardless of Count

Posted on 2006-04-18
8
Medium Priority
?
276 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:timponder
  • 4
  • 2
  • 2
8 Comments
 
LVL 16

Expert Comment

by:RCorfman
ID: 16484337
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.
0
 
LVL 18

Assisted Solution

by:Plucka
Plucka earned 1000 total points
ID: 16484339
timponder,

This should do it

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

Regards
Plucka
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16484349
Duh. Thanks Plucka. I was thinking an outer join would solve it, but for some reason my mind went numb... or was that dumb.... ;)
0
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
LVL 16

Expert Comment

by:RCorfman
ID: 16484355
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.
0
 
LVL 18

Expert Comment

by:Plucka
ID: 16484599
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
0
 

Author Comment

by:timponder
ID: 16486213
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

0
 
LVL 16

Accepted Solution

by:
RCorfman earned 1000 total points
ID: 16486851
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  :(
0
 

Author Comment

by:timponder
ID: 16486966
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month16 days, 16 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question