Hi Bryan, Your suggestion worked really well. I have one additional question though. The UNION ALL returns all information from both select statements; howeve r, when I output the query I noticed that if a dept is missing from either result, it will fill in the data of the one that exists. Is there some wa y that I can check for this and not allow this to happen. I assume that the answer is no and I have to find some other way to return my results.
T -----Original Message----- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 5:01 PM To: CF-Talk Subject: RE: Problem with QuerySetCell That's correct. This will work: SELECT DeptName, Count(*) AS appCount, 1 AS isOnline FROM apps WHERE IsOnline = 1 GROUP BY DeptName UNION ALL SELECT DeptName, Count(*) AS appCount, 0 AS isOnline FROM apps WHERE IsOnline = 0 GROUP BY DeptName ORDER BY DeptName, isOnline +-----------------------------------------------+ 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 hav e peace'..." - Thomas Paine, The American Crisis -----Original Message----- From: Tammy Hong [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 2:36 PM To: CF-Talk Subject: RE: Problem with QuerySetCell Thanks Bryan. I will work on this but the problem I see right away with either SQL statement is that SQL Server won't allow grouping by bit data type. I will see if there is a workaround. Tammy -----Original Message----- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 4:11 PM To: CF-Talk Subject: RE: Problem with QuerySetCell A couple points here... Actually this would be the better query to use: SELECT count(*) AS appCount, deptName, isOnline FROM apps GROUP BY deptName, isOnline ORDER BY deptName, isOnline but if you want to use a UNION then do this... - add 'isOnline' to the SELECT statement and to the ORDER BY statement to get this: SELECT DeptName, isOnline, Count(*) FROM TableName WHERE IsOnline = 1 GROUP BY DeptName UNION ALL SELECT DeptName, isOnline, Count(*) FROM TableName WHERE IsOnline = 0 GROUP BY DeptName ORDER BY DeptName, isOnline ordering is done after the UNION so ordering by deptName then isOnline wi ll yield a result set like this... row deptName isOnline appCount ------------------------------------------------------ 1 A 0 15 2 A 1 22 3 B 0 4 4 B 1 13 etc..... The key to outputting the results lies in realizing that each deptName sp ans two rows: one for online apps and one for offline apps... <cfoutput query="myquery" group="deptname"> #appOwner# -offline:#myquery.appCount# -online:#myquery.appCount[currentRow+1]#<br> </cfoutput> +-----------------------------------------------+ 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 hav e peace'..." - Thomas Paine, The American Crisis -----Original Message----- From: Tammy Hong [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 1:46 PM To: CF-Talk Subject: RE: Problem with QuerySetCell 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 Dum= QueryAddColumn(qryGetOnlineApps,"OfflineApps",NullArray)><CFLOOPquery="qryGetOnlineApps"> <CFSET Du= QuerySetCell(qryGetOnlineApps,"OfflineApps",qryGetOfflineApps.TotalOffli neAp p,qryGetOnlineApps.CurrentRow)> </CFLOOP> Tammy ______________________________________________________________________ Dedicated Windows 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=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
