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 will
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 spans
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 have
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 Dummy=
QueryAddColumn(qryGetOnlineApps,"OfflineApps",NullArray)>
<CFLOOP query="qryGetOnlineApps"> <CFSET Dumm=
QuerySetCell(qryGetOnlineApps,"OfflineApps",qryGetOfflineApps.TotalOffli
neAp
p,qryGetOnlineApps.CurrentRow)>
</CFLOOP>
Tammy
______________________________________________________________________
Get Your Own 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=coldfusionb
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
