For Access try SUM instead of COUNT
<cfquery name="countyesnos" datasource="#dsn#">
SELECT SUM(IIF(success = 1, 1, 0)) AS successyes,
SUM(IIF(success = 0, 1, 0)) AS successno
FROM survey
</cfquery>
P.
-----Original Message-----
From: Jaime Garza [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 27, 2000 7:24 PM
To: CF-Talk
Subject: RE: access iif & count
Power to the sub queries!
FOR SQL Server:
SELECT
(SELECT Count(*) FROM Survey WHERE success=1) as SuccessYes,
(SELECT Count(*) FROM Survey WHERE success=0) as SuccessNo,
(SELECT Count(*) FROM Survey WHERE OtherFlag=32) as CountThirtyTwos
For Oracle, same thing but you add 'FROM dual' at the end:
Access should work as SQL server but I have not tried months ago.
<Jaime/>
-----Original Message-----
From: Deanna L. Schneider [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 27, 2000 11:17 AM
To: CF-Talk
Subject: access iif & count
Hi folks,
I'm trying to use IIF in a query to count a bunch of yes/no values. Here's
what I'm trying that doesn't work (both count the total rows):
<cfquery name="countyesnos" datasource="#dsn#">
SELECT COUNT(IIF (success = 1, 1, 0)) as successyes,
COUNT(iif (success = 0, 1, 0)) as successno
FROM survey</cfquery>
I know I could do this:
SELECT COUNT(success) as successyes
FROM survey
WHERE success = 1
to get the right answer, but I want to be able to do it for several yes/no
fields all in one query. I know I'm just putting something in the wrong
place or something. Help?
************************************************************
Deanna Schneider
Interactive Media Developer
UWEX Cooperative Extension Electronic Publishing Group
103 Extension Bldg
432 N. Lake Street
Madison, WI 53706
(608) 265-7923
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.