jeudi 19 septembre 2019

How to add not existing record and return it with zero value in Mysqli

QUERY:

 SELECT month(date_created), count(a.ticket_num) 
            FROM ticket as a 
            LEFT JOIN user_management as b on b.engineer_id = a.ticket_engineer 
            WHERE b.tl_id = 'sample_id'
            AND year(date_created) = '2019'
            GROUP BY extract(year from date_created), extract(month from date_created)

SAMPLE OUTPUT:

  month | ticket_num
----------------------
    2   |     12
    4   |     24
    6   |     78

EXPECTED SAMPLE OUTPUT:

 month | ticket_num
----------------------
    1   |     0
    2   |     12
    3   |     0
    4   |     24
    5   |     0
    6   |     78

As you can see the above expected output, i'm trying to place all existing month in the first column and set all the count to zero if not existed in the second column. As of now, i only have the query for sorting the ticket count by month that is existed when the ticket is created.




Aucun commentaire:

Enregistrer un commentaire