With all those tables in there, you may be getting a lot more records than
you expected. Your current syntax will return a count of the number of
records in the joined structure, not a count of the number of unique
ChildID's. If that's what you want, change it to COUNT(DISTINCT
ChildIdentity.ChildID). Note that there will be a performance penalty for
doing this, as the database will have to sort the records.
Bob
-----Original Message-----
From: John Wilker [mailto:[EMAIL PROTECTED]]
Sent: March 28, 2001 1:34 PM
To: CF-Talk
Subject: Query boggle
Hey guys and gals,
This is probably something I'm just not seeing clearly but the below
query is supposed to bring my back a count of records grouped by sex.
I've been staring at this query so long I'm not seeing anything. It finds
the right records. The data I have has a male and a female record and both
are found by this query. Yet when I output TotalPerGender I get 808233984
Much obliged.
<CFQUERY DATASOURCE="#dbname#" NAME="qGetSectionTwo">
SELECT
COUNT(ChildIdentity.ChildID) AS TotalPerGender,
ChildIdentity.ChildGender AS Gender
FROM
vvl_Counties ,
vvl_districts,
vvl_schools,
ChildSchoolHistory CSH,
ChildIdentity INNER JOIN ChildResidence ON ChildIdentity.ChildID =
ChildResidence.ChildID
WHERE
ChildIdentity.ChildID = CSH.ChildID
AND Year(CSH.SchoolYearStartDate) BETWEEN #FORM.SchoolYear#
AND CSH.schooltitle1 = 1<!--- Not sure what value --->
AND CSH.LastAttendDate IS NULL
AND ChildResidence.ResidenceCounty = '#FORM.counties#'
GROUP BY ChildIdentity.ChildGender
ORDER BY ChildIdentity.ChildGender
</CFQUERY>
J.
John Wilker
Web Applications Consultant
Allaire Certified ColdFusion Developer
Office: 909-943-8428
www.red-omega.com
15 people are known to have been crushed to death tilting soda
machines towards them in order to receive free soda.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
