Subquery in SUM - 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 2008 Forums

Transact-SQL (2008)

Subquery in SUM

Author

Topic

DeanMc
Starting Member

3 Posts

Posted - 2011-12-13 : 07:13:02

I'm trying to use a subquery to provide part of a calculation.

In the following, I'd like to replace the number "4" with the result of "SELECT COUNT(*) FROM BCMM_Questions WHERE MaturityLevelID = 1", but I'm not sure where the subquery should go.

SELECT BCMM_MaturityLevel.MaturityLevel, SUM((BCMM_Answers.Score * (BCMM_Questions.ScorePercentage / 100)) / 4) AS Score
FROM BCMM_Answers INNER JOIN
BCMM_Questions ON BCMM_Answers.QuestionID = BCMM_Questions.QuestionID INNER JOIN
BCMM_MaturityLevel ON BCMM_Questions.MaturityLevelID = BCMM_MaturityLevel.MaturityLevelID
WHERE BCMM_Answers.AssessmentID = 1
GROUP BY BCMM_MaturityLevel.MaturityLevel, BCMM_Questions.MaturityLevelID
HAVING BCMM_Questions.MaturityLevelID = 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 07:31:11

[code]
SELECT BCMM_MaturityLevel.MaturityLevel, SUM((BCMM_Answers.Score * (BCMM_Questions.ScorePercentage / 100.0)) / NULLIF((SELECT COUNT(*) FROM BCMM_Questions WHERE MaturityLevelID = 1),0)) AS Score
FROM BCMM_Answers INNER JOIN
BCMM_Questions ON BCMM_Answers.QuestionID = BCMM_Questions.QuestionID INNER JOIN
BCMM_MaturityLevel ON BCMM_Questions.MaturityLevelID = BCMM_MaturityLevel.MaturityLevelID
WHERE BCMM_Answers.AssessmentID = 1
GROUP BY BCMM_MaturityLevel.MaturityLevel, BCMM_Questions.MaturityLevelID
HAVING BCMM_Questions.MaturityLevelID = 2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DeanMc
Starting Member

3 Posts

Posted - 2011-12-13 : 07:35:55

Thanks for your reply, but it generates the following error:

Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

DeanMc
Starting Member

3 Posts

Posted - 2011-12-13 : 08:18:32

I've sorted it out. If anyone else is interested, here is the answer;

SELECT BCMM_MaturityLevel.MaturityLevel, (SUM(BCMM_Answers.Score * BCMM_Questions.ScorePercentage / 100) / (SELECT COUNT(*) FROM BCMM_Questions WHERE MaturityLevelID = 1)) AS Score FROM BCMM_Answers INNER JOIN BCMM_Questions ON BCMM_Answers.QuestionID = BCMM_Questions.QuestionID INNER JOIN BCMM_MaturityLevel ON BCMM_Questions.MaturityLevelID = BCMM_MaturityLevel.MaturityLevelID WHERE(BCMM_Answers.AssessmentID =1) GROUP BY BCMM_MaturityLevel.MaturityLevel, BCMM_Questions.MaturityLevelID HAVING(BCMM_Questions.MaturityLevelID =1)

DeanMc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 08:44:57

will have a problem if count is zero causing divide by 0 error which is why i put NULLIF((count query),0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources