Solved

Counts per record

Posted on 2006-11-23
7
246 Views
Last Modified: 2008-03-17
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
Comment
Question by:arvanhalleorg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18003301
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
 
LVL 15

Expert Comment

by:mcmonap
ID: 18003309
SELECT
  QuestionNum
  , Answer
  , COUNT(*) CountAnswer
FROM
  myTable
GROUP BY
  QuestionNum
  , Answer
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 18003323
oops, missed the 0 answers off in my query...
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 2

Author Comment

by:arvanhalleorg
ID: 18003404
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18003448

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
 
LVL 2

Author Comment

by:arvanhalleorg
ID: 18003477
gives me invalid column name 'answer' line 1

where does the select 2 union all select 3 etc come from?
0
 
LVL 2

Author Comment

by:arvanhalleorg
ID: 18003585
ah got it, thanks :)
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question