Solved

Counts per record

Posted on 2006-11-23
7
250 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

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 article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

623 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