When there is no matching data instead of returning 0 rows, Is there a way to return a column with a zero?

Question

I have this query.

SELECT  
  FACTORY,  
  SUBSTR(dt,1,20 ) AS 'DATE',  
  SUM(count_a) AS 'PRODUCT A',  
  SUM(count_b) AS 'PRODUCT B', 
  SUM(count_a + count_b) AS 'TOTAL' 
FROM LIST 
WHERE DATE BETWEEN '2018-12-01' AND '2018-12-05'
GROUP BY COUNTRY, SUBSTR(dt,1,20)
ORDER BY COUNTRY, SUBSTR(dt,1,20);

There is no matching data in '2018-12-03'
So which returns value like this

FACTORY   |    DATE    |   PRODUCT A |  PRODUCT B |  TOTAL
-------------------------------------------------------------
Factory_A | 2018-12-01 |         5000|      10000 |   15000
Factory_A | 2018-12-02 |        10000|       5000 |   15000
Factory_A | 2018-12-04 |        10000|      10000 |   20000
Factory_A | 2018-12-05 |        15000|       5000 |   20000

But I want to it return 0 when no row matches above criteria.

like this

FACTORY |     DATE     | PRODUCT A | PRODUCT B |   TOTAL
----------------------------------------------------------
Factory_A | 2018-12-01 |       5000|     10000 |   15000 
Factory_A | 2018-12-02 |      10000|      5000 |   15000
**Factory_A | 2018-12-03 |          0|          0|       0**
Factory_A | 2018-12-04 |      10000|      10000|   20000
Factory_A | 2018-12-05 |      15000|       5000|   20000

Is there any way to put zero in a column instead of return 0 rows in MYSQL?

Answer 1

you may consider to use right outer join.

For this solution you need to have CALENDAR table which holds all your dates.

SELECT  
  FACTORY,  
  SUBSTR(Calendar.Date,1,20 ) AS 'DATE',  
  SUM(count_a) AS 'PRODUCT A',  
  SUM(count_b) AS 'PRODUCT B', 
  SUM(count_a + count_b) AS 'TOTAL' 
FROM LIST 
RIGHT OUTER JOIN CALENDAR ON list.dt = Calendar.Date
WHERE DATE BETWEEN '2018-12-01' AND '2018-12-05'
GROUP BY COUNTRY, SUBSTR(dt,1,20)
ORDER BY COUNTRY, SUBSTR(dt,1,20);