Simple query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Simple query

Author

Topic

paulc100
Starting Member

1 Post

Posted - 2005-10-25 : 07:18:29

Hi guys,

I wonder if someone could help me with this simple query, (not very good with SQL)

I have 3 tables in Access 2003
Questions - A list of questions
Answers - A list of possible answers to these questions
ClientResponse - The actual answers from the user

I am trying to create a summary query along the following lines
SELECT
ClientResponse.ContactEmail,
SUM(Answers.Score) AS TotalScore,
(SELECT SUM(Answers.Score) FROM Answers WHERE Answers.QuestionID = ClientResponse.QuestionID)
FROM
Answers,
ClientResponse
WHERE
(Answers.AnswerId = ClientResponse.AnswerId)
GROUP BY
ClientResponse.ProjectCode,
ClientResponse.ContactEmail

however this doesn't work it says I need QuestionID as part of the aggregate function, when I do this I no longer have a distinct ContactEmail.

Any pointers would be great.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-25 : 10:12:06

I think this is what you want...

SELECT	ClientResponse.ProjectCode,
	ClientResponse.ContactEmail, 
	SUM(Answers.Score) AS TotalScore,
	QuestionTotals.QuestionTotal
FROM	ClientResponse
	inner join Answers on ClientResponse.AnswerId = Answers.AnswerId
	inner join --QuestionTotals
		(SELECT	QuestionID,
			SUM(Answers.Score) as QuestionTotal
		FROM	Answers
		GROUP BY QuestionID) QuestionTotals
		on ClientResponse.QuestionID = QuestionTotals.QuestionID
GROUP BY ClientResponse.ProjectCode, 
	ClientResponse.ContactEmail,
	QuestionTotals.QuestionTotal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources