Help with grouped query

[Captain Special]

Hello. The following query and output is working ok except that the values
diplayed for #num_staff#, #num_adult#, and #num_child# are never anything other
than 1 or 0. Could someone identify what I've been doing wrong?

Thanks. (Code attached below, first the query and then the query output.)



<CFQUERY NAME="get_site" DATASOURCE="#request.dsn#" DBTYPE="ODBC">
SELECT tbl_site.*, tbl_program.program, tbl_district.district,
tbl_state.state_name, tbl_state.state_abbrev,
COUNT(tbl_adult.ID) AS num_adult,
COUNT(tbl_child.ID) AS num_child,
COUNT(tbl_staff.ID) AS num_staff
FROM tbl_site, tbl_program, tbl_district, tbl_state,
tbl_adult, tbl_child, tbl_staff
WHERE tbl_site.program_id = tbl_program.ID
AND tbl_program.state_id = tbl_state.ID
AND tbl_program.district_id *= tbl_district.ID
AND tbl_adult.site_ID =* tbl_site.ID
AND tbl_child.site_ID =* tbl_site.ID
AND tbl_staff.site_ID =* tbl_site.ID
AND tbl_program.ID = <CFQUERYPARAM VALUE="#form.ID#"
CFSQLTYPE="CF_SQL_INTEGER">

GROUP BY tbl_adult.ID, tbl_child.ID, tbl_staff.ID,
tbl_site.ID, tbl_site.site, tbl_site.site_ID, tbl_site.program_ID,
tbl_site.district_ID,
tbl_site.site_coord, tbl_site.site_tech,
tbl_site.site_address1, tbl_site.site_address2, tbl_site.site_city,
tbl_site.site_state_ID, tbl_site.site_zip, tbl_site.mailing_address1,
tbl_site.mailing_address2, tbl_site.mailing_city, tbl_site.mailing_zip,
tbl_site.site_phone, tbl_site.site_fax,
tbl_site.site_email, tbl_site.driving_directions, tbl_site.notes,
tbl_program.program, tbl_district.district,
tbl_state.state_name, tbl_state.state_abbrev

<CFIF IsDefined("URL.sort") AND URL.sort EQ "id">
ORDER BY tbl_site.site_ID, tbl_site.site
<CFELSEIF IsDefined("URL.sort") AND URL.sort EQ "program">
ORDER BY tbl_program.program, tbl_site.site
<CFELSEIF IsDefined("URL.sort") AND URL.sort EQ "district">
ORDER BY tbl_district.district, tbl_site.site
<CFELSE>
ORDER BY tbl_site.site, tbl_site.site_ID
</CFIF>
</CFQUERY>



<CFOUTPUT QUERY="get_site" GROUP="site">
<BR>
<TABLE BORDER=1 CELLPADDING=1 CELLSPACING=0 WIDTH=400>
<TR><TD BGCOLOR="White" ALIGN="left" WIDTH=35%>Site</TD>
<TD BGCOLOR="White" ALIGN="left" WIDTH=65%><SPAN
CLASS="fixed"><B>#get_site.site#</B></SPAN></TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">Site ID</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN
CLASS="fixed"><B>#get_site.site_id#</B></SPAN>&nbsp;</TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">Program</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN
CLASS="fixed">#get_site.program#</SPAN>&nbsp;</TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">District</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN
CLASS="fixed">#get_site.district#</SPAN>&nbsp;</TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">Coordinator</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN
CLASS="fixed">#get_site.site_coord#</SPAN>&nbsp;</TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">Technologist</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN
CLASS="fixed">#get_site.site_tech#</SPAN>&nbsp;</TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">Address</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN CLASS="fixed">#get_site.site_address1#
<CFIF get_site.site_address2 NEQ
""><BR>#get_site.site_address2#</CFIF><BR>
#get_site.site_city#, #UCase(get_site.state_abbrev)#
#get_site.site_zip#</SPAN></TD></TR>

<CFIF get_site.mailing_address1 NEQ ""
OR get_site.mailing_address2 NEQ ""
OR get_site.mailing_city NEQ ""
OR get_site.mailing_zip NEQ "">
<TR><TD BGCOLOR="White" ALIGN="left">Mailing Address</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN
CLASS="fixed">#get_site.mailing_address1#
<CFIF get_site.mailing_address2 NEQ
""><BR>#get_site.mailing_address2#</CFIF><BR>
#get_site.mailing_city#, #UCase(get_site.state_abbrev)#
#get_site.mailing_zip#</SPAN></TD></TR>
</CFIF>

<TR><TD BGCOLOR="White" ALIGN="left">Phone</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN
CLASS="fixed">#get_site.site_phone#</SPAN>&nbsp;</TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">Fax</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN
CLASS="fixed">#get_site.site_fax#</SPAN>&nbsp;</TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">Email</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN
CLASS="fixed">#get_site.site_email#</SPAN>&nbsp;</TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">Number Enrolled Staff</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN CLASS="fixed"><CFOUTPUT
GROUP="num_staff">#get_site.num_staff#</CFOUTPUT></SPAN>&nbsp;</TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">Number Enrolled Adult Participants</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN CLASS="fixed"><CFOUTPUT
GROUP="num_adult">#get_site.num_adult#</CFOUTPUT></SPAN>&nbsp;</TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">Number Enrolled Child Participants</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN CLASS="fixed"><CFOUTPUT
GROUP="num_child">#get_site.num_child#</CFOUTPUT></SPAN>&nbsp;</TD></TR>

</TABLE>
</CFOUTPUT>

[Dan Bracuk]

My guess is that you are selecting too many fields.

How many fields are in tbl_site? Do you really need them all?

[Captain Special]

Yes, I do need to pull all those fields - they are actually listed manually in
the GROUP BY section. The display page is for summary data, that my users can
see all entered sites - with all pertinent info - in one fell swoop.

[Dan Bracuk]

If you have to display all that data, run your initial select without the count() stuff in it. Then use Q of Q to get your summary data.

[kim il sung]

perhaps your joins are not returning nulls?
what db you use?

try COUNT(ISNULL(tbl_adult.ID, 0)) AS num_adult
kim

[Captain Special]

This application runs on MS SQL Server 2000. I tried the COUNT(ISNULL(tbl_adult.ID, 0)) for all three count fields, and the results showed 1, 1 and 1 each.

[paross1]

First of all, you probably should change the old obsolete style outer join
syntax(=* and *=) and rewrite it using the ANSI SQL-92 LEFT or RIGHT JOIN
syntax instead. May save you lots of heartache later.

Also, I don't know how you would expect to see any count other than 1 for any
of your ID counts, since you are equating tbl_site.ID to tbl_adult.site_ID AND
tbl_child.site_ID AND tbl_staff.site_ID for each row selected, then grouping by
these fields. How would your count ever exceed one? In other words, you are
only going to get a distinct row when all three are the same value, unless I'm
missing the boat here.

Phil

[Captain Special]

Well, strangely enough it seems to be missing the count even when I eliminate
tbl_child and tbl_staff entirely from the equation. Here is a simplified
query, as shown in the debugger:

SELECT tbl_site.*, tbl_program.program, tbl_district.district,
tbl_state.state_name, tbl_state.state_abbrev,
COUNT(tbl_adult.ID) AS num_adult
FROM tbl_site, tbl_program, tbl_district, tbl_state,
tbl_adult
WHERE tbl_site.program_id = tbl_program.ID
AND tbl_program.state_id = tbl_state.ID
AND tbl_program.district_id *= tbl_district.ID
AND tbl_adult.site_ID =* tbl_site.ID
AND tbl_program.ID = #form.program_ID#

GROUP BY tbl_adult.ID,
tbl_site.ID, tbl_site.site, tbl_site.site_ID, tbl_site.program_ID,
tbl_site.district_ID,
tbl_site.site_coord, tbl_site.site_tech,
tbl_site.site_address1, tbl_site.site_address2, tbl_site.site_city,
tbl_site.site_state_ID, tbl_site.site_zip, tbl_site.mailing_address1,
tbl_site.mailing_address2, tbl_site.mailing_city, tbl_site.mailing_zip,
tbl_site.site_phone, tbl_site.site_fax,
tbl_site.site_email, tbl_site.driving_directions, tbl_site.notes,
tbl_program.program, tbl_district.district,
tbl_state.state_name, tbl_state.state_abbrev


ORDER BY tbl_site.site, tbl_site.site_ID


Like the more complex, first iteration, this query properly returns both sites
from the db, but still lists the number of adult participants for each as just
1 (there are quite a few more than that).

The code for the output can be reduced to this for simplification:

<CFOUTPUT QUERY="get_site" GROUP="site">
<BR>
<TABLE BORDER=1 CELLPADDING=1 CELLSPACING=0 WIDTH=400>
<TR><TD BGCOLOR="White" ALIGN="left" WIDTH=35%>Site</TD>
<TD BGCOLOR="White" ALIGN="left" WIDTH=65%><SPAN
CLASS="fixed"><B>#get_site.site#</B></SPAN></TD></TR>

<TR><TD BGCOLOR="White" ALIGN="left">Number Enrolled Adult Participants</TD>
<TD BGCOLOR="White" ALIGN="left"><SPAN CLASS="fixed"><CFOUTPUT
GROUP="num_adult">#get_site.num_adult#</CFOUTPUT></SPAN>&nbsp;</TD></TR>

</TABLE>
</CFOUTPUT>

[paross1]

....probably because you are only getting one of each row for all of the fields
that you are grouping by. It is hard to determine what it is that you want to
do when you are only showing us what it is that you are trying to do.

Phil

[Captain Special]

I see. I apologize for not being clearer. In essence, I want the summary page
to display a little table with all the pertinent data for each site within the
criteria selected by the previous page. Each table in the summary page has the
standard name, address, etc. But I also want the table to simply display the
number of adult participants associated with each site, as well as child
participants and staff members. Each adult participant has a single record on
tbl_adult, and each contains the foreign key site_ID which relates each adult
record to one site. The same is true for both child participants and staff
members.

The sample I'm running has anywhere from 6-12 adults, children, and staff
listed for each of the two sites I have in the dummy data. So, what I want to
do is get a result that contains something like the following:

Site A
Number of adult participants: 6
Number of child participants: 8
Number of staff members: 11


Site B
Number of adult participants: 10
Number of child participants: 10
Number of staff members: 4


I hope that makes it clearer. I am very grateful for the help.

[paross1]

Then you should only be selecting grouping by siteID, tbl_adult.ID,
tbl_child.ID, and tbl_staff.ID. Adding all of the other fields to the same
query is what is messing you up.

Just off of the top of my head, would something like this much simplified
query do what something like what you are trying to do?

Phil



SELECT tbl_site.ID, tbl_program.program
(SELECT COUNT(tbl_adult.ID)
FROM tbl_adult
WHERE tbl_adult.site_ID = tbl_site.ID) AS num_adult,
(SELECT COUNT(tbl_child.ID)
FROM tbl_child
WHERE tbl_child.site_ID = tbl_site.ID) AS num_child,
(SELECT COUNT(tbl_staff.ID)
FROM tbl_staff
WHERE tbl_staff.site_ID = tbl_site.ID) AS num_staff
FROM tbl_site, tbl_program
WHERE tbl_site.program_id = tbl_program.ID
AND tbl_program.state_id = tbl_state.ID
AND tbl_program.ID = #form.program_ID#

[Captain Special]

Thank you - that worked beautifully, even with more detailed versions of the query.