Ah ok... beginner's SQL course for me. Thanks everyone. d
----- Original Message ----- From: "Stephen Moretti" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, June 11, 2003 5:03 PM Subject: Re: [ cf-dev ] percentages of values in recordset > > > > ok -- I really have confused things! Maybe it's the way I've asked the > > question. I have a solution though that at least saves me querying lots, > > it'll help you see what I'm doing. > > > > <cfparam name="Value1" default=0> > > ...same for 2,3,4,5 > > > > <cfquery name="QueryName"> > > SELECT ColumnName, COUNT(ColumnName) AS Count > > FROM TableName > > </cfquery> > > > One quick note, don't use count as the alias for the count(columnname) - > even ColCount is better. Count is a reserved word (or potentially is) so > you need to avoid using them as column names, variables etc. > > > <cfloop query="QueryName"> > > <cfif ColumnName EQ 1> > > <cfset Value1=Value1 + 1> > > <cfelseif ColumnName EQ 2> > > <cfset Value2=Value2 + 1> > > etc etc... > > </cfif> > > </cfloop> > > > If you put GROUP BY ColumnName on the end of your query above, then you > don't need to do this. The COUNT(ColumnName) will return the Value1 through > Value5 along side in the query. > > > Then to get the percentage: > > > > <cfoutput query="QueryName">#DecimalFormat(Evaluate((Value1 / Count) * > > 100))# %</cfoutput> > > > If you put the GROUP BY on the end of the query, then use Rich's bit of > script to add up the total number of Poll responses > > <cfset totalresponses = > arraysum(listtoarray(valuelist(QueryName.ColCount)))> > > You can output the percentages like this : > > Total Responses : <cfoutput>#totalresponses#</cfoutput> > <cfoutput query="QueryName"> > <cfset ColPercent = (ColCount/TotalResponses) *100> > #ColumnName# - #ColCount# - #DecimalFormat(ColPercent)#% > </cfoutput> > > The query with the GROUP BY is already totaling up the number of responses > per option for you, so all you need to do is work out the total number of > responses from the query (Rich's code) and then you have 5 (the number of > options in your poll) rows in a query to work out the percentages for. > > Hope this helps. > > Regards > > Stephen > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] > > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
