null values in mysql

Question

null values in mysql

Navigation

#1 by (1 votes)

0

I have a month table, an office table and another table detail_despacho, what I need is to be able to get the total of products sold for each month but with that I need that if in any month there is no registered office that still shows me the month saying that the total of products sold was 0, this is the query I have:

SELECT SUM(dd.dd_cantidad) cantidad, desp.des_numero numero, m_nombre
FROM detalle_despacho dd JOIN despacho desp ON dd.despacho_des_numero = desp.des_numero JOIN mes m ON MONTH(desp.des_fecha) = m_id 
WHERE YEAR(desp.des_fecha) = 2018 AND dd.producto_pr_id = 1000 GROUP BY MONTH(desp.des_fecha)

the year is just an example but if I select 2018 I need to see from January to December and that those months without offices only show me 0, I hope your help, thanks!

mysql php

asked by Roberto Antonio Rico Palma 26.05.2018 в 19:23

source

1 answer

The property 'map' does not exist in the type 'ObservableUser' How to get data from table roles the views through the Auth methods in laravel?

score 1 · Answer 1

SELECT 
   SUM(ifnull(dd.dd_cantidad, 0)) cantidad, desp.des_numero numero, m_nombre 
FROM 
   detalle_despacho dd 
JOIN despacho desp ON dd.despacho_des_numero = desp.des_numero
RIGHT  JOIN mes m ON MONTH(desp.des_fecha) = m_id 
WHERE 
   YEAR(desp.des_fecha) = 2018 
AND dd.producto_pr_id = 1000 
GROUP BY 
  MONTH(desp.des_fecha)

You have to use right join with the months table, so all the records in the months table are shown even though they do not have a detail, in this case the amount will be NULL, the function IFNULL() is used to set to 0 the value of the quantity column when it is NULL.