getting count less 0 entries

[ranger]

i am building a survey site where selections are: Strongly Agree = 5 Agree = 4
Neither Agree nor Disagree = 3 Disagree = 2 Strongly Disagree = 1 Not
Applicable = 0 To chart output, i have done a query: <cfset catcnt = 96>
<cfset catcnt1 = #catcnt# - 1> <cfquery name='gtavg' datasource='surveyz'
dbtype='ODBC'> SELECT <cfloop index=catz from=1 to=#catcnt1# step=1>
AVG(s#catz#) AS a#catz#, </cfloop> AVG(s#catcnt#) AS a#catcnt# FROM
client_table ; </cfquery> where i loop thru table to get average of each of 96
questions. PROBLEM.... 'Not Applicable' should not be weighed into averages
and throws off survey. is there any way to ignore (or count non) '0' entries
in loop? otherwise i will have to create a query for each of 96 fields... ugh!

[kim il sung]

small example of where clause, check your db docs to lear more

<cfquery name="gtavg" datasource="surveyz" dbtype="ODBC">
SELECT
<cfloop index=catz from=1 to=#catcnt1# step=1>
AVG(s#catz#) AS a#catz#,
</cfloop>
AVG(s#catcnt#) AS a#catcnt#
where
<cfloop index=catz from=1 to=#catcnt1# step=1>
s#catz# <> 0
</cfloop>
FROM client_table ;
</cfquery>

[Der Nickname]

Too much commas without an AND :)

<cfquery name="gtavg" datasource="surveyz" dbtype="ODBC">
SELECT
<cfloop index=catz from=1 to=#catcnt1# step=1>
AVG(s#catz#) AS [a#catz#] <cfif catz lt catcnt1>, </cfif>
</cfloop>
AVG(s#catcnt#) AS [a#catcnt#]
where
<cfloop index=catz from=1 to=#catcnt1# step=1>
s#catz# <> 0 <cfif catz lt catcnt1> AND </cfif>
</cfloop>
FROM client_table ;
</cfquery>

[ranger]

tried your ideas and seems to be one problem... remember, this is a survey and
can be hundreds++ of rows. your loop in the WHERE statement follows the loop
in the SELECT statement. so would loop thru all 96 fields in SELECT, 96
times...? am i confused??? limiter '<> 0' needs to affect the SELECT
statement as am only looping table 1 time for all AVG am i missing something
here? also there already is a WHERE statement that i skipped to simplify
discussion: WHERE (memdt IS NOT NULL) AND (NOT empLocation = 'test') ; tnx in
advance

[zoeski80]

Hi Ranger

not sure if you'd be able to do this in all one query as if you put a where
statement in of s1 <> 0 then it will knock the whole row out of the query
statement.

I would do one query on the database to get all the answer records and then do
QoQ over the 96 columns to get the average for each answer.

HTH

Zoe

<!--- do 1 query on database --->
<cfquery name="getAnswers" datasource="surveyz" dbtype="ODBC">
SELECT *
FROM client_table
WHERE memdt IS NOT NULL
AND empLocation <> 'test'
</cfquery>

<!--- loop through all columns and get the average for each column - exclude 0
values --->
<cfloop index=catz from=1 to=#catcnt# step=1>
<cfquery name="getAnswers#catz#" DBTYPE="QUERY">
SELECT avg(s#catz#) as a#catz#
FROM getAnswers
WHERE s#catz# <> 0
</cfquery>
<CFDUMP VAR="#Evaluate('getAnswers#catz#')#" LABEL="getAnswers#catz#">
</CFLOOP>

[Der Nickname]

Stack your loops:
<cfquery name="gtavg" datasource="surveyz" dbtype="ODBC">
SELECT
<cfloop index=catz from=1 to=#catcnt1# step=1>
AVG(s#catz#) AS [a#catz#] <cfif catz lt catcnt1>, </cfif>
AVG(s#catcnt#) AS [a#catcnt#]
where
<cfloop index=catz from=1 to=#catcnt1# step=1>
s#catz# <> 0 <cfif catz lt catcnt1> AND </cfif>
</cfloop>
</cfloop>
FROM client_table ;
</cfquery>