CODEWITHSUNDEEP
mysql
Christian D.
Christian D.

Reputation: 65

MySQL return 'empty result' even with coalesce

I have some trouble with MySQL.

Here is the query I use:

SELECT
    COALESCE(SUM(`a`.`battles`), 0) AS `battles`
FROM
    `account_stats` AS `a`
WHERE
    `a`.`account_id` = 12345
GROUP BY
    `a`.`account_id`

The Table account_stats is not empty, but has no row with account_id = 12345.

I want that MySQL returns 0 battles instead of Empty set. But even with COALSECE or IFNULL it returns Empty set.

When I remove the GROUP BY everything works fine, but I need it to calculate the SUM of battles.

Is there a way to workaround this problem?

Upvotes: 0

Views: 244

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If you only want information on one account, you can use conditional aggregation if you want the query to return a row with the value of 0:

SELECT SUM(CASE WHEN a.account_id = 12345 THEN a.battles ELSE 0 END) as battles
FROM account_stats a;

If the table is not empty, then you don't need coalesce().

If you have an index on account_id and the table is big, the following would probably be more efficient because the subquery would use the index and the rest of the query would be manipulating a single row:

SELECT x.account_id, COALESCE(SUM(a.battles), 0) as battles
FROM (SELECT 12345 as account_id
     ) x LEFT JOIN
     (SELECT a.account_id, SUM(a.battles) as battles
      FROM account_stats a
      WHERE a.account_id = 12345
     ) a
     ON x.account_id = a.account_id;

Upvotes: 2

Related Questions