MySql selecting default value if there are no resu

Question

i'm having 2 tables: members and comments. I select all members, and then join comments. But in comments I'm selecting some SUM of points, and if user never commented, I can't get that user in listing?!

So how to select default value for SUM to be 0 if user never commented, or some other solution:

SELECT c.comment_id AS item_id, m.member_id AS member_id, m.avatar, 
            SUM(c.vote_value) AS vote_value, SUM(c.best) AS best, 
            SUM(c.vote_value) + SUM(c.best)*10 AS total
            FROM members m
            LEFT JOIN comments c ON m.member_id = c.author_id
            GROUP BY c.author_id
            ORDER BY m.member_id DESC
            LIMIT 0, 20

---

EDIT:

I will try to explain... So there are 2 tables, members and comments. I need listing of all users with ranking. Comments hold all votes and best answers.

So, I need listing of all users, and they score.

Members table:

member_id - username - avatar

Comments table

comment_id - author_id - vote_value - best (0 OR 1)

Also tried to select from COMMENTS and join MEMBERS, but same thing again :(

Answer 1

I'm not sure why you are including the comment_id in your SELECT list if you just want users and their rankings. Do you want only their ranking on that particular comment? I'll give a solution for now that assumes you just want a full member list with rankings:

SELECT
    M.member_id,
    M.user_id,
    M.avatar,
    COALESCE(SUM(C.vote_value), 0) AS vote_value_sum,
    COALESCE(SUM(C.best), 0) AS best_sum,
    COALESCE(SUM(C.vote_value), 0) + SUM(C.best) * 10 AS total_value
FROM
    Members M
LEFT OUTER JOIN Comments C ON
    C.author_id = M.member_id
GROUP BY
    M.member_id
ORDER BY
    total_value DESC
LIMIT 0, 20

(this assumes that vote_value and best are NOT NULL columns or that MySQL will disregard those when calculating SUM values - I believe that it does, but I haven't tested that)

Answer 2

You could use the if statement to convert NULL to 0

SELECT c.comment_id AS item_id, ...
    IF(SUM(c.vote_value) is null, 0, SUM(c.vote_value)) as vote_value
FROM members m
LEFT JOIN comments c ON ...

Answer 3

I use COALESCE for this sort of thing.

http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_coalesce

I would rewrite your second and third lines as:

COALESCE(SUM(c.vote_value), 0) AS vote_value, COALESCE(SUM(c.best),0) AS best,
COALESCE(SUM(c.vote_value), 0) + COALESCE(SUM(c.best),0) * 10 AS total

Answer 4

MySQL has an IFNULL operator which allows you to return a value other than null if the result is null.

SELECT c.comment_id AS item_id, m.member_id AS member_id, m.avatar, 
            SUM(IFNULL(c.vote_value, 0)) AS vote_value, SUM(IFNULL(c.best, 0)) AS best, 
            SUM(IFNULL(c.vote_value, 0)) + SUM(IFNULL(c.best, 0))*10 AS total
            FROM members m
            LEFT JOIN comments c ON m.member_id = c.author_id
            GROUP BY c.author_id
            ORDER BY m.member_id DESC
            LIMIT 0, 20

As others mentioned, COALESCE does something similar (and also works in MySQL).

Answer 5

I don't think the SUM is the problem. I think it may be your

GROUP BY c.author_id

where you are grouping by the right side of a left outer join. I'm not sure what that does when the right side doesn't exist, but I'm guessing that's not what you really want. You most likely should be grouping by something in members, not comments.

Having said that, yes, you should also use COALESCE or IFNULL to turn null values to 0.

Answer 6

You could have a look to the CASE Statement, too: http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

In your case the result would be similar to...

SELECT c.comment_id AS item_id, m.member_id AS member_id, m.avatar, 
        (CASE SUM(c.vote_value) WHEN NULL THEN 0  
        ELSE SUM(c.vote_value) END) AS vote_value, SUM(c.best) AS best, 
        (CASE SUM(c.vote_value) + SUM(c.najbolji)*10 WHEN null THEN 0
        ELSE SUM(c.vote_value) + SUM(c.najbolji)*10 END) AS total
        FROM members m
        LEFT JOIN comments c ON m.member_id = c.author_id
        GROUP BY c.author_id
        ORDER BY m.member_id DESC
        LIMIT 0, 20