Set default value if no result found

Question

I have the following query

SELECT count(*) as count, Month(created_at) as month 
FROM products 
WHERE marketplace_id=21
      and status='counterfeit' 
      and created_at < Now() 
      and created_at > DATE_ADD(Now(), INTERVAL - 5 MONTH)
group by month(created_at)

it return result as

+-------+-------+
| count | month |
+-------+-------+
|   410 |     1 |
|   174 |     2 |
|   301 |     3 |
|   329 |     4 |
|   141 |    12 |
+-------+-------+

in case a month does not have values it doesn't returns it at all, but I want the default value 0 to be set for that month.

I have tried this link Return a default value if no rows found and

Returning a value if no result

I am not sure whether I am not able to implement it correctly or this is not what I want

May be create a calendar table and store only the months (12 rows) and do a left join to that table. — Abhik Chakraborty, May 06 '16 at 07:16

Mippy · Answer 1 · 2016-05-06T07:37:48.967

Try this, seems to be a little stupid, but may help for you;)

SELECT SUM(count) AS count, month
FROM (
    SELECT count(*) as count, Month(created_at) as month FROM products WHERE marketplace_id=21
            and status='counterfeit' and created_at < Now() and created_at > DATE_ADD(Now(), INTERVAL - 5 MONTH)
            group by month(created_at)
    UNION 
    SELECT * FROM (
        SELECT 0 AS count, 1 AS month
        UNION SELECT 0 AS count, 2 AS month
        UNION SELECT 0 AS count, 3 AS month
        UNION SELECT 0 AS count, 4 AS month
        UNION SELECT 0 AS count, 5 AS month
        UNION SELECT 0 AS count, 6 AS month
        UNION SELECT 0 AS count, 7 AS month
        UNION SELECT 0 AS count, 8 AS month
        UNION SELECT 0 AS count, 9 AS month
        UNION SELECT 0 AS count, 10 AS month
        UNION SELECT 0 AS count, 11 AS month
        UNION SELECT 0 AS count, 12 AS month) M
    WHERE M.month < Month(Now()) AND M.month > Month(DATE_ADD(Now(), INTERVAL - 5 MONTH)))
) tmp
GROUP BY mouth
ORDER BY month

ok this works, but the problem here is it is giving value for whole year, what if I am starting from august and want data of past six months Aug, Jul, Jun,May,Apr and Mar — Khan Shahrukh, May 06 '16 at 07:25
Hmm, what you guys said is definitely right, I've updated my post, check it again pls. — Mippy, May 06 '16 at 07:41

Buksy · Answer 2 · 2016-05-24T06:40:08.423

You could create another table with default values

  test_defaults
-----------------
| month | count |

and than just left join it with your table of values so if the value is found within the main table, it will be used, if not value from test_defaults would be used (we will use COALESCE function which returns first non null value):

SELECT t1.month, COALESCE(t2.count, t1.count)
FROM test_defaults t1
LEFT JOIN test_data t2 ON t1.month = t2.month
ORDER BY t1.month;

Here's a working SqlFiddle demo

Set default value if no result found

2 Answers2