How to Bring in Full 24 Month Span (including NULL values) in MySQL Query Count

Attempting to create a ‘Previous Year’ vs ‘Current Year’ Graph using a jQuery plug-in with data from my MySQL database.

The data for such is like this

   var previousyear = [
        [1,  24monthsago],
        [2,  23monthsago],
        [3,  22monthsago],
        [4,  21monthsago],
        [5,  20monthsago],
        [6,  19monthsago],
        [7,  18monthsago],
        [8,  17monthsago],
        [9,  16monthsago],
        [10, 15monthsago],
        [11, 14monthsago],
        [12, 13monthsago]
    ];
    var currentyear = [
        [1,  12monthsago],
        [2,  11monthsago],
        [3,  10monthsago],
        [4,  9monthsago],
        [5,  8monthsago],
        [6,  7monthsago],
        [7,  6monthsago],
        [8,  5monthsago],
        [9,  4monthsago],
        [10, 3monthsago],
        [11, 2monthsago],
        [12, 1monthago]
    ];

if we pretend the xmonthsago is an integer value.

The case I am working for is a database for an Inventory System and I am looking to create a 24-month span for sold counts on certain SKUs using my inventory_history table.

I have used this current query with success:

SELECT Date_format(lastmodified, '%Y') AS 'year', 
       Date_format(lastmodified, '%m') AS 'month', 
       Count(id)                       AS 'total' 
FROM   inventory_history 
WHERE  inventory_history.sku = 'SKU_NAME' 
GROUP  BY Date_format(lastmodified, '%Y%m');

Which, for example, outputs:

╔══════╦═══════╦═══════╗
║ year ║ month ║ total ║
╠══════╬═══════╬═══════╣
║ 2017 ║    08 ║     2 ║
║ 2017 ║    12 ║     1 ║
║ 2018 ║    01 ║     1 ║
║ 2018 ║    03 ║     1 ║
║ 2019 ║    02 ║     1 ║
╚══════╩═══════╩═══════╝

I am wondering if it is possible to streamline this query even more to give me a proper output which includes all months in between that do not have any COUNTs

Output something like this:

╔══════╦═══════╦═══════╗
║ year ║ month ║ total ║
╠══════╬═══════╬═══════╣
║ 2017 ║    08 ║     2 ║
║ 2017 ║    09 ║     0 ║
║ 2017 ║    10 ║     0 ║
║ 2017 ║    11 ║     0 ║
║ 2017 ║    12 ║     1 ║
║ 2018 ║    01 ║     1 ║
║ 2018 ║    02 ║     0 ║
║ 2018 ║    03 ║     1 ║
║ 2018 ║    04 ║     0 ║
║ 2018 ║    05 ║     0 ║
║ 2018 ║    06 ║     0 ║
║ 2018 ║    07 ║     0 ║
║ 2018 ║    08 ║     0 ║
║ 2018 ║    09 ║     0 ║
║ 2018 ║    10 ║     0 ║
║ 2018 ║    11 ║     0 ║
║ 2018 ║    12 ║     0 ║
║ 2019 ║    01 ║     0 ║
║ 2019 ║    02 ║     1 ║
║ 2019 ║    03 ║     0 ║
║ 2019 ║    04 ║     0 ║
║ 2019 ║    05 ║     0 ║
║ 2019 ║    06 ║     0 ║
║ 2019 ║    07 ║     0 ║
║ 2019 ║    08 ║     0 ║
╚══════╩═══════╩═══════╝

Anyone have any idea on how I may accomplish this with a MySQL query?

Answer

If you have data in the table for all months, the simplest method is probably conditional aggregation:

SELECT year(lastmodified) AS year, 
       month(lastmodified) AS month, 
       SUM(ih.sku = 'SKU_NAME') AS total 
FROM inventory_history ih
GROUP BY year, month;

Notes:

• There is no reason to use date_format() when MySQL has convenient built-in functions.
• Only use single quotes for string and date constants, not for column aliases especially.
• You don’t need to escape your column aliases.
• The GROUP BY columns should match the unaggregated columns in the SELECT. They can use aliases.
• The SUM() uses a very convenient feature of MySQL. It treats booleans as integers, with 1 for true and 0 for false.