got it, I needed an END in my case statement. Here's what it looks like:
SELECT
COUNT(ALL CASE WHEN bit_complete = 'n' THEN 1 ELSE NULL END) AS
incompleteTasks,
COUNT(*) AS totalTasks
FROM TASKS
WHERE
num_peopleID = 184
thanks for any replies.
ps. please ignore my bit_field = '', I needed to change it to a string to
accept Pending (p) status.
Adam.
> -----Original Message-----
> From: Cantrell, Adam [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, March 21, 2002 4:06 PM
> To: CF-Talk
> Subject: multiple aggregrates in single query
>
>
> Anyone know how to get this in SQLserver without two queries?
> I'm outputting
> something like: "10 incomplete out of 23 for user Joe Shmoe".
> I know this is
> totally wrong, but maybe someone will get my drift:
>
> <CFQUERY>
> SELECT
> tIncomplete.count(*) AS incompleteTasks,
> tTotal.count(*) AS totalTasks
> FROM TASKS AS tIncomplete, TASKS AS tTotal
> WHERE
> tIncomplete.num_peopleID = 184 AND
> tIncomplete.bit_complete = 0 AND
> tTotal.num_peopleID = 184
> </CFQUERY>
>
>
> I could just do it in two queries, but I'm really trying to
> do this project
> right this time - hehe.
>
> Adam.
>
>
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
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
