I need to select the data stored in the database and separate them by month, but when the month does not have data yet, the month is not returned.
I need the month to be returned with the value zero but to bring the month anyway.
My Query looks like this:
SELECT
(year(article.content_path)) as anoAtual,
MONTHNAME(STR_TO_DATE(month(article.content_path), '%m')) AS MES,
TIME_FORMAT(SEC_TO_TIME(Sum(time_accounting.time_unit)*60),"%H:%i") as tempo_total,
concat(users.first_name, ' ',customer_user.last_name) as usuario,
customer_user.email
FROM
article,
time_accounting,
users,
customer_user
where
article.id = time_accounting.article_id and
article.create_by = users.id and
EXTRACT(YEAR FROM article.content_path) IN (2018) and
users.first_name = customer_user.first_name and
users.last_name like concat(customer_user.last_name,'%') and
customer_user.customer_id = empresa and
users.valid_id = 1 and
customer_user.email in (emails)
group by
article.create_by,
year(article.content_path),
MES
order by
article.create_by,
year(article.content_path),
month(article.content_path)
I get the following return:
InthiscaseIneedthemonthofDecemberstilltocome,eventhoughIdonothaveanyrecordsyetthismonth.I'vetriedsomethingsimilarwithwhatIneedwithasimplerQuery:
SELECTmonth(article.change_time)asmes,SEC_TO_TIME(SUM(IFNULL(TIME_TO_SEC(STR_TO_DATE(TIMEDIFF((SELECTvalue_dateFROMotrs.dynamic_field_valuewhereobject_idin(t2.object_id)andfield_id=79),(SELECTvalue_dateFROMotrs.dynamic_field_valuewhereobject_idin(t2.object_id)andfield_id=78)),'%H:%i:%s')),0)))astempoDeslocamentoFROMotrs.dynamic_field_valuet2,otrs.articlewhereotrs.article.id=t2.object_idandyear(article.change_time)=2018andarticle.create_by=9andfield_idin(78)groupbymonth(article.change_time);
Butevenso,itonlyreturnstherecordsinthemonthsthathaverecords.
9 and month 12 , but IFNULL should not handle this?