how can i return 0 using the count function in sql

Question

I have the following sql query, that returns the grade 3 students made on a test.
Student 1 made a 0 on the exam, so my query result is only return the grades of two students how can i include the zero in the result?

select u.uno, u.fname, u.lname, count(q.qno) as Test1 from ((users u
LEFT OUTER JOIN userresponse ur on ur.uno = u.uno) left outer join
question q on q.correctanswer = ur.response and q.qno = ur.qno and
q.eno = ur.eno) where q.eno = '1' group by u.uno, u.fname, u.lname

ur.eno is exam 1, then question numbers and exam number are joined and q.corectanswer = ur.response checks what questions the user got right ur.uno = u.uno just joins the tables.

For what database? This is why not to use ANSI-89 JOIN syntax (your query) -- there's no OUTER JOIN support, but a subselect would be equivalent (but less preferrable -- correlated subquery effectively). — OMG Ponies, Mar 11 '12 at 07:09
@OMGPonies - there are deprecated outer join facilities in most databases (usually based around decorating the comparisons with `+` or `*`), but agree that it would be far better to move to ANSI joins — Damien_The_Unbeliever, Mar 11 '12 at 07:30
@Damien_The_Unbeliever: Yes, there are but they are also vendor specific. [SQL Server](http://stackoverflow.com/questions/1428144/sql-what-does-mean), [Oracle](http://stackoverflow.com/questions/2425960/oracle-old-joins-a-tool-script-for-conversion) — OMG Ponies, Mar 11 '12 at 15:33

Thomas Padron-McCarthy · Answer 1 · 2012-03-11T07:53:32.137

If I understand your database schema correctly, the third student, who should get the 0 result, has no corresponding rows in the userresponse table? A normal ("inner") join, like the one you are using, will drop the students who have no rows to join them with in the userresponse table. An outer join, on the other hand, will keep rows even if there is no rows to connect them to in the other table. So you should use an outer join between the table users and the table userresponse.

EDIT:

Something like this (not tested):

SELECT user.uno, user.fname, user.lname, 
       (COUNT(question.qno)*5)/75 as TESTRESULT1
  FROM ((user LEFT OUTER JOIN userresponse ON ....)
        LEFT OUTER JOIN question ON ....)
 WHERE
    ....
GROUP BY user.uno, user.fname, user.lname

Add your join conditions after the ON's, and additional conditions after the WHERE. The first outer join will keep all the students, but with null values in the columns from userresponse for the students with no responses. The second outer join will still keep the students without responses, with null values in the columns from the table questions.

Note that you need to change COUNT(*) to COUNT(question.qno), since COUNT(*) counts rows, and each student without responses will now have a row in the result.

thats correct student 3 will be null because none of his answer match the questions correct answer i looked up outer join and tried q.correctanswer = ur.response(+) but it gives me an error — user541597, Mar 11 '12 at 07:23
I am not able to get this to work. I use (userresponse ur left outer join question q on q.correctresponse = ur.response) i then need to include the user table which needs to be joined with userresponse table. I don't understand your formatting. — user541597, Mar 11 '12 at 07:57

how can i return 0 using the count function in sql

1 Answers1