mysql - SQL task if the product has no sales in the given month, then display 0 in this month

Question

I have to build and SQL query which must do these things:

1. select all products from table "products" - satisfied
2. SUM all sales and forecast to the next 3 months - satisfied
3. check if the product has no one sale, then write "0" -> here is the problem, because I don't know how to do that..

My SQL query is here..

select?product.name,
       (select?sum(amount)
        from forecast 
        where?forecast.product_id?=?product.id),
       sum(sale.amount)
from?product?join
    ?sale
?    on?sale.product_id?=?product.id
where?sale.outlook?>?-4
group?by?product.id

Here is the products table:

    id  name
    1   milk
    2   roll
    3   ham

Table sale (same structure like forecast):

product_id  outlook amount
1           -1      9
1           -2      13
1           -3      14
2           -1      88
2           -3      61
3           -1      33
3           -4      16


    

question from:https://stackoverflow.com/questions/65905935/sql-task-if-the-product-has-no-sales-in-the-given-month-then-display-0-in-this

Answer 1

You can use left join to bring in the rows and coalesce() to get the 0 instead of NULL:

select p.name,
       (select sum(f.amount)
        from forecast f 
        where v.product_id = p.id),
       coalesce(sum(s.amount), 0)
from product p left join
     sale s
     on sale.product_id = product.id and
        sale.outlook > -4
group by p.id