No yelling - but can you explain what you want as a final result, based on
these queries?
-----Original Message-----
From: Deanna L. Schneider [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 26, 2000 10:16 AM
To: [EMAIL PROTECTED]
Subject: Re: grouping with math functions
<snip>
This might best be done within the SQL statement . Can you give us a little
more detail about the table and what fields need to be divided by what?
</snip>
Okay, you asked for it. The SQL is a total mess. It's an access database
that someone else designed. So, here's the sql (don't anyone yell at me
about using a kajillion queries to get there....it's been a long week).
<cfquery name="selecttrials" datasource="#application.dsn#">
SELECT tri_id from trial
where <cfif isdefined("form.year")>
trial.seedyr IN (#preservesinglequotes(form.year)#)
</cfif>
<cfif (isdefined("form.state")) AND (isdefined("form.year"))>
AND
</cfif>
<cfif isdefined("form.state")>
trial.state IN (#preservesinglequotes(form.state)#)
</cfif>
</cfquery>
<cfquery name="limittrials" datasource="#application.dsn#">
SELECT tri_id
FROM result
WHERE tri_id IN (#valuelist(selecttrials.tri_id)#)
AND var_id = #listgetat(form.var_id, 1)#
</cfquery>
<cfquery name="limittrials2" datasource="#application.dsn#">
SELECT tri_id
FROM result
WHERE tri_id IN (#valuelist(selecttrials.tri_id)#)
AND var_id = #listgetat(form.var_id, 2)#
</cfquery>
<cfquery name="getaggresults" datasource="#application.dsn#">
SELECT result.tri_id
FROM result
WHERE result.tri_id IN (#valuelist(limittrials.tri_id)#)
AND result.tri_id IN (#valuelist(limittrials2.tri_id)#)
GROUP BY tri_id
</cfquery>
<cfquery name="getaggresults2" datasource="#application.dsn#">
SELECT AVG(yield_ta) AS avgyield, variety,
standage, COUNT(result.tri_id) AS totaltrials
FROM trial, result, variety
WHERE trial.tri_id = result.tri_id
AND result.var_id = variety.var_id
AND result.tri_id IN (#valuelist(getaggresults.tri_id)#)
AND result.var_id IN (#form.var_id#)
GROUP BY standage, variety
</cfquery>
*shudder*
-d
************************************************************
Deanna Schneider
Interactive Media Developer
UWEX Cooperative Extension Electronic Publishing Group
103 Extension Bldg
432 N. Lake Street
Madison, WI 53706
(608) 265-7923
----------------------------------------------------------------------------
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
