Well I sort of got what you are trying to explain. I created a UNION SQL statement and it is giving me the correct recordcount.
SELECT DeptName, Count(*) FROM TableName WHERE IsOnline = 1 GROUP BY DeptName UNION ALL SELECT DeptName, Count(*) FROM TableName WHERE IsOnline = 0 GROUP BY DeptName ORDER BY DeptName I am not sure how to output the results. I have tried and it only returns the first SQL SELECT statement result. What should I be doing? Tammy -----Original Message----- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 2:23 PM To: CF-Talk Subject: RE: Problem with QuerySetCell Ouch. This is not something you want to do... Query results should be joined using the UNION operator in SQL. Many people don't realize that you can hard-code query columns to aid in this effort like so... SELECT appID, appName, '1' AS isOnline FROM apps WHERE whatever IS NOT NULL UNION SELECT appID, appName, '0' AS isOnline FROM apps WHERE whatever IS NULL It looks almost like you are trying to query for total online apps and total offline apps (per owner?) in this instance however... SELECT count(*) AS appCount, appOwner, isOnline FROM apps GROUP BY appOwner, isOnline ORDER BY appOwner, isOnline You will end up with a query like this... appOwner isOnline appCount ----------------------------------------- 1 0 15 1 1 22 2 0 4 2 1 13 etc..... You can output the results like so... <cfoutput query="myquery" startrow="2" group="appOwner"> #appOwner# - offline:#myquery.appCount[currentrow-1]# - online:#appCount#<br> There are other ways to output, but this one is relatively simple. You could also use a conditional loop or a FOR loop (from 2 to recordcount step 2). Anyway, don't use CF to do what the DB does best... +-----------------------------------------------+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +-----------------------------------------------+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -----Original Message----- From: Tammy Hong [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 11:48 AM To: CF-Talk Subject: Problem with QuerySetCell Hi, I am trying to add the values from a second query to my first query but for some reason it is setting all rows to the first value of the query. Can someone tell me what I did wrong? TIA. <CFSET NullArray = ArrayNew(1)> <CFLOOP INDEX="Row" FROM="1" TO="#qryGetOnlineApps.RecordCount#"> <CFSET NullArray[#Row#] = "0"> </CFLOOP> <CFSET Dummy= QueryAddColumn(qryGetOnlineApps,"OfflineApps",NullArray)> <CFLOOP query="qryGetOnlineApps"> <CFSET Dumm= QuerySetCell(qryGetOnlineApps,"OfflineApps",qryGetOfflineApps.TotalOffli neAp p,qryGetOnlineApps.CurrentRow)> </CFLOOP> Tammy ______________________________________________________________________ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
