I'm not sure exactly what that's going to give you even if the syntax IS 
correct, but I don't think you're going to get the results that you want.  
It would be easier to break them up into two queries.  If you're concerned 
about making two trips to the DB and back, you could either put them into 
a stored procedure or send them both at the same time to the DB.  Assuming 
you're using ADO, it would look like this:

sSQL = "SELECT 
		COUNT(AL.Answer) AS total
	FROM 
		personList P1, 
		AnswerList A1, 
		demoAnswerList D1
	WHERE 
		P1.personID = D1.personID AND 
		P1.personID = A1.personID AND 
		P1.surveyID = 2 AND 
		D1.demoID = 4 AND 
		D1.demoAnswerInt = 1 AND 
		A1.questionID = 1 AND 
		A1.answerInt = 1;
	SELECT 
		COUNT(A2.Answer) AS total
	FROM 
		personList P2, 
		AnswerList A2, 
		demoAnswerList D2 
	WHERE 
		P2.personID = D2.personID AND 
		P2.personID = A2.personID AND 
		P2.surveyID = 2 AND 
		D2.demoID = 4 AND 
		D2.demoAnswerInt = 1 AND 
		A2.questionID = 1 AND 
		A2.answerInt = 2; "
Dim A1Cnt, A2Cnt
oRS.Open sSQL, oConn, adOpenForwardOnly, adLockReadOnly
A1Cnt = oRS("total")
set oRS = oRS.NextRecordset
A2Cnt = oRS("total")
oRS.Close
set oRS = Nothing

Hope that helps.

Clear skies



> 
Hello List,

Can you get multiple COUNT()s in the same recordset?

And if you can, is this the right way to do it?

	SELECT 
		COUNT(AL.Answer) AS D1A1, 
		COUNT(A2.Answer) AS D1A2 
	FROM 
		personList P1, 
		AnswerList A1, 
		demoAnswerList D1, 
		personList P2, 
		AnswerList A2, 
		demoAnswerList D2 
	WHERE 
		P1.personID = D1.personID AND 
		P1.personID = A1.personID AND 
		P1.surveyID = 2 AND 
		D1.demoID = 4 AND 
		D1.demoAnswerInt = 1 AND 
		A1.questionID = 1 AND 
		A1.answerInt = 1 AND 
		P2.personID = D2.personID AND 
		P2.personID = A2.personID AND 
		P2.surveyID = 2 AND 
		D2.demoID = 4 AND 
		D2.demoAnswerInt = 1 AND 
		A2.questionID = 1 AND 
		A2.answerInt = 2"


I'm not sure if this is going to get the right information but I wanted to
know if the snytax was correct, or if you can even do, it before I went any
further.

Thanks in advance for any help,

Rich


This communication is for use by the intended recipient and contains 
information that may be privileged, confidential or copyrighted under
applicable law.  If you are not the intended recipient, you are hereby
formally notified that any use, copying or distribution of this e-mail,
in whole or in part, is strictly prohibited.  Please notify the sender
by return e-mail and delete this e-mail from your system.  Unless
explicitly and conspicuously designated as "E-Contract Intended",
this e-mail does not constitute a contract offer, a contract amendment,
or an acceptance of a contract offer.  This e-mail does not constitute
a consent to the use of sender's contact information for direct marketing
purposes or for transfers of data to third parties.

 Francais Deutsch Italiano  Espanol  Portugues  Japanese  Chinese  Korean

            http://www.DuPont.com/corp/email_disclaimer.html

You can also do:

SELECT
    (SELECT COUNT(*) FROM myTable WHERE ...) AS myTableCount,
    COUNT(*) AS myOtherTableCount
FROM
    myOtherTable
WHERE
    ...

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Philhouse" <spam@t...>
Subject: [asp_databases] Re: Multiple COUNT()s in one recordset


: I'm not sure exactly what that's going to give you even if the syntax IS
: correct, but I don't think you're going to get the results that you want.
: It would be easier to break them up into two queries.  If you're concerned
: about making two trips to the DB and back, you could either put them into
: a stored procedure or send them both at the same time to the DB.  Assuming
: you're using ADO, it would look like this:
:
: sSQL = "SELECT
: COUNT(AL.Answer) AS total
: FROM
: personList P1,
: AnswerList A1,
: demoAnswerList D1
: WHERE
: P1.personID = D1.personID AND
: P1.personID = A1.personID AND
: P1.surveyID = 2 AND
: D1.demoID = 4 AND
: D1.demoAnswerInt = 1 AND
: A1.questionID = 1 AND
: A1.answerInt = 1;
: SELECT
: COUNT(A2.Answer) AS total
: FROM
: personList P2,
: AnswerList A2,
: demoAnswerList D2
: WHERE
: P2.personID = D2.personID AND
: P2.personID = A2.personID AND
: P2.surveyID = 2 AND
: D2.demoID = 4 AND
: D2.demoAnswerInt = 1 AND
: A2.questionID = 1 AND
: A2.answerInt = 2; "
: Dim A1Cnt, A2Cnt
: oRS.Open sSQL, oConn, adOpenForwardOnly, adLockReadOnly
: A1Cnt = oRS("total")
: set oRS = oRS.NextRecordset
: A2Cnt = oRS("total")
: oRS.Close
: set oRS = Nothing
:
: Hope that helps.
:
: Clear skies
:
:
:
: >
: Hello List,
:
: Can you get multiple COUNT()s in the same recordset?
:
: And if you can, is this the right way to do it?
:
: SELECT
: COUNT(AL.Answer) AS D1A1,
: COUNT(A2.Answer) AS D1A2
: FROM
: personList P1,
: AnswerList A1,
: demoAnswerList D1,
: personList P2,
: AnswerList A2,
: demoAnswerList D2
: WHERE
: P1.personID = D1.personID AND
: P1.personID = A1.personID AND
: P1.surveyID = 2 AND
: D1.demoID = 4 AND
: D1.demoAnswerInt = 1 AND
: A1.questionID = 1 AND
: A1.answerInt = 1 AND
: P2.personID = D2.personID AND
: P2.personID = A2.personID AND
: P2.surveyID = 2 AND
: D2.demoID = 4 AND
: D2.demoAnswerInt = 1 AND
: A2.questionID = 1 AND
: A2.answerInt = 2"
:
:
: I'm not sure if this is going to get the right information but I wanted to
: know if the snytax was correct, or if you can even do, it before I went
any
: further.
:
: Thanks in advance for any help,
:
: Rich
:
:
: This communication is for use by the intended recipient and contains
: information that may be privileged, confidential or copyrighted under
: applicable law.  If you are not the intended recipient, you are hereby
: formally notified that any use, copying or distribution of this e-mail,
: in whole or in part, is strictly prohibited.  Please notify the sender
: by return e-mail and delete this e-mail from your system.  Unless
: explicitly and conspicuously designated as "E-Contract Intended",
: this e-mail does not constitute a contract offer, a contract amendment,
: or an acceptance of a contract offer.  This e-mail does not constitute
: a consent to the use of sender's contact information for direct marketing
: purposes or for transfers of data to third parties.
:
:  Francais Deutsch Italiano  Espanol  Portugues  Japanese  Chinese  Korean
:
:             http://www.DuPont.com/corp/email_disclaimer.html
:
: