RE: Counting and looping
!--- Calculate totals for each major cfset stCount = StructNew() cfloop query=summary cfif NOT StructKeyExists(stCount,summary.major) cfset stCount[summary.major] = 0 /cfif cfset stCount[summary.major] = stCount[summary.major] + summary.tstuds /cfloop cfdump var=#stCount# Pascal PS1: If ID is your PK, distinct, count(id) and GROUP BY are just overhead PS2: Need I repeat?? CFQUERYPARAM !!! -Original Message- From: James Watkins [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 23:28 To: CF-Talk Subject: Counting and looping I am trying to count individual id's in each major but can't see to get the thing right. Any help would be appreciated: (Oracle SQL) CFQUERY NAME=summary DATASOURCE=NGTbanner SELECT distinct majr_code1 major, id, count(id) tstuds, last_name, first_name, styp_code stype, camp_code, total_credit_hours chrs, ests_code rstatus, sysdate, decode(camp_code,'1','Clarkesville','2','Blairsville','3','Currahee','9' ,'Internet') camp from as_student_enrollment_summary where term_code_key = '#term#' and levl_code = '50' and registered_ind = 'Y' and camp_code like '#campus#' Group by majr_code1, id, last_name, first_name, styp_code, camp_code, total_credit_hours, ests_code, sysdate order by #sort# /CFQUERY ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184845 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Counting and looping
Jim, The loop you have only appends all the totals to the one variable. So you are probably getting a total for all variables. What you need to do is within the loop dynamically set the variable to each majorcode and append to that variable instead of to the one variables. I think if you used the query loop option you would have much better success with this. cfloop query=queryname This way you can set which major code it is on by record reference and the total to that variable. HTH Mark -Original Message- From: James Watkins [mailto:[EMAIL PROTECTED] Sent: Thursday, November 18, 2004 3:28 PM To: CF-Talk Subject: Counting and looping I am trying to count individual id's in each major but can't see to get the thing right. Any help would be appreciated: (Oracle SQL) CFQUERY NAME=summary DATASOURCE=NGTbanner SELECT distinct majr_code1 major, id, count(id) tstuds, last_name, first_name, styp_code stype, camp_code, total_credit_hours chrs, ests_code rstatus, sysdate, decode(camp_code,'1','Clarkesville','2','Blairsville','3','Currahee','9' ,'Internet') camp from as_student_enrollment_summary where term_code_key = '#term#' and levl_code = '50' and registered_ind = 'Y' and camp_code like '#campus#' Group by majr_code1, id, last_name, first_name, styp_code, camp_code, total_credit_hours, ests_code, sysdate order by #sort# /CFQUERY !--- Calculate totals for each major cfset numamount=0 cfset numend=#summary.recordcount# cfloop from=1 to=#numend# index=i cfset numamount=numamount+#summary.tstuds[i]# /cfloop Jim Watkins VP Technology Institutional Services 706 781-2305 - Blairsville 706 754-7850 - Clarkesville [EMAIL PROTECTED] http://www.northgatech.edu ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184830 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Counting and looping
Probably a way to do it within ur SQL or how about using QofQ? cfquery name=idbymajor dbtype=query SELECT count(id) as cnt FROM summary GROUP BY major /cfquery Doug, On Thu, 18 Nov 2004 15:56:32 -0700, Broner, Mark, CON, OASD(HA)/TMA [EMAIL PROTECTED] wrote: Jim, The loop you have only appends all the totals to the one variable. So you are probably getting a total for all variables. What you need to do is within the loop dynamically set the variable to each majorcode and append to that variable instead of to the one variables. I think if you used the query loop option you would have much better success with this. cfloop query=queryname This way you can set which major code it is on by record reference and the total to that variable. HTH Mark -Original Message- From: James Watkins [mailto:[EMAIL PROTECTED] Sent: Thursday, November 18, 2004 3:28 PM To: CF-Talk Subject: Counting and looping I am trying to count individual id's in each major but can't see to get the thing right. Any help would be appreciated: (Oracle SQL) CFQUERY NAME=summary DATASOURCE=NGTbanner SELECT distinct majr_code1 major, id, count(id) tstuds, last_name, first_name, styp_code stype, camp_code, total_credit_hours chrs, ests_code rstatus, sysdate, decode(camp_code,'1','Clarkesville','2','Blairsville','3','Currahee','9' ,'Internet') camp from as_student_enrollment_summary where term_code_key = '#term#' and levl_code = '50' and registered_ind = 'Y' and camp_code like '#campus#' Group by majr_code1, id, last_name, first_name, styp_code, camp_code, total_credit_hours, ests_code, sysdate order by #sort# /CFQUERY !--- Calculate totals for each major cfset numamount=0 cfset numend=#summary.recordcount# cfloop from=1 to=#numend# index=i cfset numamount=numamount+#summary.tstuds[i]# /cfloop Jim Watkins VP Technology Institutional Services 706 781-2305 - Blairsville 706 754-7850 - Clarkesville [EMAIL PROTECTED] http://www.northgatech.edu ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184841 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
