count - 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

SQL Server Development (2000)

count

Author

Topic

php95saj
Starting Member

43 Posts

Posted - 2002-10-23 : 07:33:41

I am building a survey, in which user will answer a set of questions and the repsonse is stored in the database table.
I want to build a report on the response saying how many users answered what to a question (for example).
table:
====================================
responseID | question1 | question2 |
====================================
1 | 0 | 2 |
2 | 2 | 2 |
3 | 1 | 1 |
4 | 2 | 2 |
5 | 3 | 1 |
6 | 4 | 1 |
7 | 2 | 3 |
8 | 1 | 3 |

So for example in the report, I should say something like:

question1:
resp0 1
resp1 2
resp2 3
resp3 1
resp4 1

etc...

Thanks

jeroena
Yak Posting Veteran

76 Posts

Posted - 2002-10-23 : 07:44:55

Yes... ?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-10-23 : 15:22:04

I'm not really clear what you're asking for, but if you're asking for how to summarize the results, you might begin by looking into using the GROUP BY command, and perhaps a CASE statement in your query. MAYBE what you're going to want is a cross-tab, and you can find more info on those here.

transio
Starting Member

7 Posts

Posted - 2002-10-24 : 12:42:59

Assuming that you have a table structure such as this:

Users
UserId (PK)
UserName

Questions
QuestionId (PK)
Question

Answers
AnswerId (PK)
QuestionId (FK)
Answer

Responses (or UserAnswers)
UserId (CK, FK)
QuestionId (CK, FK)
AnswerId (FK)

Try a query like this:

SELECT
Â Â q.QuestionId,
Â Â q.Question,
Â Â a.AnswerId,
Â Â a.Answer,
Â Â r.ResponseCount
FROM
Â Â (SELECT QuestionId, AnswerId, COUNT(UserId) AS ResposeCount
Â Â FROM Responses
Â Â GROUP BY QuestionId, AnswerId) AS r
Â Â INNER JOIN Questions AS q ON r.QuestionId = q.QuestionId
Â Â INNER JOIN Answers AS a ON r.AnswerId = a.AnswerId
ORDER BY q.QuestionId, a.AnswerId

-------------------------
Steve (aka SteQL)

http://www.synthelogic.com
http://www.transio.com

Edited by - transio on 10/24/2002 12:44:25

Edited by - transio on 10/24/2002 12:45:05

Edited by - transio on 10/24/2002 12:46:20

Edited by - transio on 10/24/2002 12:46:47

Edited by - transio on 10/24/2002 12:47:23

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources