• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Counts per record

I'm having a little trouble thinking about how to do a query.

Let's say the first table has

QuestionNum
1

and in the second table there's this

QuestionNum AnswerChoice
1                    1
1                    1
1                    2
1                    1
1                    5

how could I end up with something that looks like this

QuestionNum Answer CountAnswer
1                     1          3
1                     2          1
1                     3          0
1                     4          0
1                     5          1

If anyone can help, thanks ahead of time :)
0
arvanhalleorg
Asked:
arvanhalleorg
  • 3
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select q.questionnum, a.answer, sum(case when qa.answerchoice is null then 0 else 1 end) as CountAnswer
from Questions q
cross join ( select 1 as answer union all select 2 union all select 3 union all select 4 union all select 5 ) as a
left join QuestionAnswers qa
  on qa.questionnum = q.questionnum
 and qa.answer = q.answer
group by q.questionnum, a.answer
0
 
mcmonapCommented:
SELECT
  QuestionNum
  , Answer
  , COUNT(*) CountAnswer
FROM
  myTable
GROUP BY
  QuestionNum
  , Answer
0
 
mcmonapCommented:
oops, missed the 0 answers off in my query...
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
arvanhalleorgAuthor Commented:
angelIII i get this error:

Invalid column name 'answer'.
i made 2 tables to test with, the Question and QuestionAnswers

Question has QuestionNum
QuestionAnswers has QuestionNum and AnswerChoice

am I missing something?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

select q.questionnum, a.answer, sum(case when qa.answerchoice is null then 0 else 1 end) as CountAnswer
from Questions q
cross join ( select 1 as answer union all select 2 union all select 3 union all select 4 union all select 5 ) as a
left join QuestionAnswers qa
  on qa.questionnum = q.questionnum
 and qa.answerchoice = q.answer
group by q.questionnum, a.answer
0
 
arvanhalleorgAuthor Commented:
gives me invalid column name 'answer' line 1

where does the select 2 union all select 3 etc come from?
0
 
arvanhalleorgAuthor Commented:
ah got it, thanks :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now