• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

mySQL query help sum zero

I have 3 tables:

TABLE-A - groups
GID     Group Name
-----     ------
1          GROUPA
2          GROUPB
3          GROUPC


TABLE-B - questions
QID     Question
-----    --------
1         How do you feel? (Scale 1-10)
2         How old are you? (Scale 1-10)
3        1 + 1? (Scale 1-10)

TABLE-C - questionResponses
RID     QID      GID       Result
-----    --------    --------    --------
1         1            1            2  -
2         1            2            5  -
3         2            3            6
4         3            1            1  -
5         2            1            9  -


I want result like this
QUERY OUTPUT

QID      GID       SUM
--------    --------    --------
1            1            2
2            1            9
3            1            1
1            2            5
2            2            0
3            2            0
1            3            0
2            3            6
3            3            0




This is as far as I have got:
SELECT Q.questionID, G.groupID, COALESCE(SUM(R.questionResponse),0)
FROM questions Q

LEFT JOIN questionResponses R
ON Q.questionID = R.questionID

LEFT JOIN groups G
ON G.groupID = R.groupID

WHERE Q.questionID IN ( 244,245,246 )

GROUP BY Q.questionID, G.groupID

ORDER BY Q.questionID

Open in new window

and the output looks like this:
query output
As you can see I am getting the sum for all 3 questions but it isnt including all 3 groups (should be 9).

Any help?
0
Steve Tinsley
Asked:
Steve Tinsley
  • 2
1 Solution
 
awking00Commented:
select x.qid, x.gid, sum(nvl(r.result,0)) results
from responses r
right join
(select q.qid, g.gid
 from questions q, groups g) x
on r.qid = x.qid and r.gid = x.gid
group by x.gid, x.qid
order by x.gid, x.qid;
0
 
Steve TinsleyAuthor Commented:
Thats brilliant! Think I need to read up on my right joins!
Thanks!
0
 
awking00Commented:
Thanks. Could have used a left join, too. :-)
select x.qid, x.gid, sum(nvl(r.result,0)) results from
(select q.qid, g.gid
 from questions q, groups g) x
left join
responses r
on r.qid = x.qid and r.gid = x.gid
group by x.gid, x.qid
order by x.gid, x.qid;
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now