assitance with aggregates
-
I need help getting some counts. Here's what I'm trying to do -
select a.question,b.answer,count(b.answer)
from questions a join answers b on a.qid=b.qid
group by a.question,b.answer
This gives me a result set like this:
A Y 15
A N 2
A U 1
B Y 8
B N 10
C Y 8
C N 9
C U 1
(etc)
The issue is it's missing "B U 0" -- how do I make sure that gets in there?
TIA
-
In your example, you are trying to count something that doesn't exist. If the number of times where the Answer to Question B was U is 0 then there are no records to count. You might want to try something like this instead:
Select
a.Question,
sum(Case b.Answer When "Y" Then 1 Else 0 End) AS [Y],
sum(Case b.Answer When "N" Then 1 Else 0 End) AS [N],
sum(Case b.Answer When "U" Then 1 Else 0 End) AS
From
questions a
inner join answers b
on a.qid=b.qid
Which would return a result set like this:
Y N U
A 15 2 1
B 8 10 0
C 8 9 1
-
Pivoting the table works as Matthew mentions...but I'd probably do it something as follows..
declare @possible_answers table
(
possible_answer char(1)
)
declare @questions table
(
question char(1)
)
declare @results table
(
question char(1),
answer char(1),
tally int default 0
)
insert @questions
select question
from questions
insert @results(question,answer)
select q.question,a.possible_answer
from @possible_answers a cross join @questions q
update @results
set tally = x.tally
from @results r
join
(
select a.question,b.answer,count(b.answer) tally
from questions a join answers b on a.qid=b.qid
group by a.question,b.answer
)q
on r.question = q.question
and r.answer = q.answer
select * from @results
This assumes that all questions have the same possible questions. You may have to redesign @possible_answers table to incorporate question,possible_answer if thats not the case.
HTH
Mathew J Kulangara
sqladventures.blogspot.com -
Well, I'm not sure what benefits that would give, nor do I really understand what you're doing. But the pivot seems to work just fine, and is only .15% more expensive than my original query. I'm glad it was that easy. Why is it called "pivot", anyway? (I mean besides taking the rows and making them columns)
-
-
Yes, a LEFT would work if every question was answered. As it is, the LEFT causes a "A NULL 0" to be added for each question.
-
Try this left join:
select a.question, c.answer, count(b.answer)
from questions a
cross join
(select 'Y' as answer union select 'N' union select 'U')
c
left join answers b on a.qid = b.qid and c.answer = b.answer
group by a.question, c.answer
Similar to Mathew K's suggestion, though...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply