try this:
select count(*) AS counter
from messages
where (responseID = 0 OR responseID = messageID) AND
projectID = #variable#
group by responseID
the group by clause will cause the count(*) function to be executed on each
grouping of responseID. The query output should contain 2 values, one for
where responseID = 0 and one for where responseID = messageID
___________________________________________
Bryan Love
Director of Development
[EMAIL PROTECTED]
MarketMatrix Inc.
810 3rd Ave
Seattle WA, 98104
206~343~9445
marketmatrix.com
___________________________________________
-----Original Message-----
From: Russ Michaels [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 03, 2000 5:34 AM
To: [EMAIL PROTECTED]
Subject: SQL aggregation prob
I have a table called messages, the 3 columns I am working with are:-
messageID, responseID, projectID
I am trying to write a single query that will give me a total count of
several columns:-
Here is a pseudo code example of what I want
variable = 1
select total messages (where responseID = 0) as threads
select total messages (where responseID = messageID) as totalmessages
where projectID = #variable#
Here is what I have been playing with that doesn't work. I know what it is
doing, it is comparing the messageID with responseID on each row
individually and finding them not the same, rather than comparing every
rows responseID against each messageID as I want.
SELECT (select count(*) from messages where projectID = #URL.projectID# and
responseID = 0) as threads,
(select count(*) from messages where projectID = #URL.projectID# and
responseID = messageID) as totalmsg
FROM messages
where projectID = #URL.projectID#
Agghh help.
----------------------------------------------------------------------------
--
Archives: http://www.eGroups.com/list/cf-talk
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.eGroups.com/list/cf-talk
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.
