Do you have a question? Post it now! No Registration Necessary. Now with pictures!
July 13, 2006, 5:22 pm
rate this thread
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
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
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 :: firstname.lastname@example.org :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
- » Prepared Statment seems to run but doesn't affect database
- — Previous thread in » PHP Scripting Forum