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.

Advertisement

Answer