Solved

Counts per record

Posted on 2006-11-23
7
208 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
SELECT
  QuestionNum
  , Answer
  , COUNT(*) CountAnswer
FROM
  myTable
GROUP BY
  QuestionNum
  , Answer
0
 
LVL 15

Expert Comment

by:mcmonap
Comment Utility
oops, missed the 0 answers off in my query...
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:arvanhalleorg
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility

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
Comment Utility
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
Comment Utility
ah got it, thanks :)
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now