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

General SQL Server Forums

New to SQL Server Programming

Simple query

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-11-22 : 15:42:03

I could not get this simple query to work.
I am doing a count on a table of responses. In the question table, I have QuestionID (int), QuestionNumber (int), QuestionText (varchar) etc. etc. In the Response table - I have ResponseID (int), QuestionID (FK), Comment (varchar), CommentValue (int) etc.

What I want is - list all the question numbers, and if matching (> 0) CommentValues are not present, put 0 as Count. So I tried:

SELECT Question.QuestionNumber, COUNT(Response.CommentValue)
FROM Response
INNER JOIN Question ON Response.QuestionID = Question.QuestionID
AND Response.Comment IS NOT NULL AND Response.Comment != ''
AND Response.CommentValue > 0
GROUP BY Question.QuestionNumber

The Problem: It returns only the question numbers where there's a positive CommentValue, instead of listing all.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-11-22 : 15:51:07

[code]SELECT q.QuestionNumber,
COUNT(r.CommentValue)
FROM dbo.Question AS q
LEFT JOIN dbo.Response AS r ON r.QuestionID = q.QuestionID
AND r.Comment > ''
AND r.CommentValue > 0
GROUP BY q.QuestionNumber
ORDER BY q.QuestionNumber;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-11-22 : 15:58:14

Ah sorry - the left join. thanks so much...I was just not trying to understand why it was not returning this simple thing - if no count, return 0. That's why the left join totally didn't come to me.

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-11-22 : 16:16:48

Also another problem:

I need to count the neutral comments as well, but the neutral commentvalues can be NULL or 0. The query returns only the one's with zeros. Like:
SELECT q.QuestionNumber,
COUNT(r.CommentValue)
FROM dbo.Question AS q
LEFT JOIN dbo.Response AS r ON r.QuestionID = q.QuestionID
AND r.Comment > ''
AND (r.CommentValue = 0 OR r.CommentValue IS NULL)
GROUP BY q.QuestionNumber
ORDER BY q.QuestionNumber;

So, apparently - OR r.CommentValue IS NULL is not doing anything. Any idea?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-11-22 : 16:40:38

[code]SELECT q.QuestionNumber,
COUNT(ISNULL(r.CommentValue, 0))
FROM dbo.Question AS q
LEFT JOIN dbo.Response AS r ON r.QuestionID = q.QuestionID
AND r.Comment > ''
AND (r.CommentValue = 0 OR r.CommentValue IS NULL)
GROUP BY q.QuestionNumber
ORDER BY q.QuestionNumber;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-11-22 : 16:43:05

Thanks so much again SwePeso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources