SQL sum problem

[HaggMan]

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.

[Andy Hassall]

Re: SQL sum problem

On 13 Jul 2006 10:22:03 -0700, "HaggMan" <haggardii@gmai l.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 :: andy@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool