SQL sum problem

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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.

Re: SQL sum problem

Quoted text here. Click to load it

 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

Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Site Timeline