By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,049 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

SQL sum problem

P: n/a
I have two tables for holding questions and the corresponding answers
of several users.

Table 1 (matt_q as q):
id, secid, weight

Table 2 (matt_ans as a):
id, qid, userid, answer

Answer can be "Yes" or "No"
There are several sections (corresponding q.secid)

I'd like to get the sum of q.weight for all the entries in Table 1
where the corresponding a.answer (joined by qid=q.id) is "no" grouped
by q.secid

This is what I have so far:
select q.secid, sum(q.weight) from matt_q as q, matt_ans as a where
a.qid=q.id and a.answer='no' group by q.secid;

This works unless the user has not answered any questions with 'no.'
In that case, there is no row returned (because the sum is null). Is
there any way to get a sum of 0?

Sorry if this is confusing.

Jul 13 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 13 Jul 2006 10:22:03 -0700, "HaggMan" <ha*******@gmail.comwrote:
>I have two tables for holding questions and the corresponding answers
of several users.

Table 1 (matt_q as q):
id, secid, weight

Table 2 (matt_ans as a):
id, qid, userid, answer

Answer can be "Yes" or "No"
There are several sections (corresponding q.secid)

I'd like to get the sum of q.weight for all the entries in Table 1
where the corresponding a.answer (joined by qid=q.id) is "no" grouped
by q.secid

This is what I have so far:
select q.secid, sum(q.weight) from matt_q as q, matt_ans as a where
a.qid=q.id and a.answer='no' group by q.secid;

This works unless the user has not answered any questions with 'no.'
In that case, there is no row returned (because the sum is null). Is
there any way to get a sum of 0?
Yes, use an outer join, and for good measure you could use coalesce on
q.weight (since IIRC the behaviour of sum() where there are nulls in the set is
not always consistent between brands of databases):

select q.secid, sum(coalesce(q.weight, 0))
from matt_q as q
left outer join matt_ans as a on (a.qid = q.id and a.answer = 'no')
group by q.secid;

One way to think about how (left) outer joins work is that where the join to
the second (right-hand) table may have gaps, these are filled in with nulls, so
that all the rows from the first table still appear (those matching the "where"
clause anyway), instead of being filtered out because the join condition is
false.

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Jul 13 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.