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.

Reply via email to