Problem with SQL addition

Question

Please,

I'm trying to get some total value of user earned points in few distinctive counts, but everything is from same table.

SQL:

SELECT o.author_id, SUM(o.vote_value) AS vote_value, n.best AS best_sum, 
                (SUM(o.vote_value) + (10 * n.best)) AS total
                FROM comments o
                LEFT JOIN (
                  SELECT COUNT(best) AS best, author_id
                  FROM comments
                  WHERE best = 1
                  GROUP BY author_id
                  ) n ON o.author_id = n.author_id
                GROUP BY o.author_id
                ORDER BY total DESC
                LIMIT 0, 4

Problem is if there is no BEST (default is 0, and best is 1) I'm getting nothing for "total" and "best_sum". I need 0 if user doesn't have "best" so that calculation can be performed right

Sorry, my bad english I think You will understood Thanks

Answer 1

I assume you want to have a 0 value if best=NULL try COALESCE

count(COALESCE(best,0) AS best)

Answer 2

You should use COALESCE(n.best, 0)

COALESCE returns the second parameters when the first one is NULL

SELECT o.author_id, SUM(o.vote_value) AS vote_value, 
            COALESCE(n.best, 0) AS best_sum, 
            (SUM(o.vote_value) + (10 * COALESCE(n.best, 0))) AS total
            FROM comments o
            LEFT JOIN (
              SELECT COUNT(best) AS best, author_id
              FROM comments
              WHERE best = 1
              GROUP BY author_id
              ) n ON o.author_id = n.author_id
            GROUP BY o.author_id
            ORDER BY total DESC
            LIMIT 0, 4

Answer 3

I think your problem is that you are filtering out the rows where best is not 1, so they aren't included in the group by. Try this:

SELECT o.author_id, SUM(o.vote_value) AS vote_value, n.best AS best_sum, 
            (SUM(o.vote_value) + (10 * n.best)) AS total
            FROM comments o
            JOIN (
              SELECT SUM(best) AS best, author_id
              FROM comments
              GROUP BY author_id
              ) n ON o.author_id = n.author_id
            GROUP BY o.author_id
            ORDER BY total DESC
            LIMIT 0, 4

Also, maybe I'm missing something but wouldn't this work, saving the subquery?

SELECT o.author_id, SUM(o.vote_value) AS vote_value, SUM(o.best) AS best_sum, 
            (SUM(o.vote_value) + (10 * SUM(o.best))) AS total
            FROM comments o
            GROUP BY o.author_id
            ORDER BY total DESC
            LIMIT 0, 4