CODEWITHSUNDEEP
mysqlsql
Khan Shahrukh
Khan Shahrukh

Reputation: 6401

Set default value if no result found

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

Upvotes: 1

Views: 215

Answers (2)

Buksy
Buksy

Reputation: 12234

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

Upvotes: 1

Mippy
Mippy

Reputation: 364

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

Upvotes: 2

Related Questions