CODEWITHSUNDEEP
mysqlsqlgroup-by
Marios
Marios

Reputation: 27360

Count for each item and for each month within a date range the number of items that are in the inventory - mysql

I have a table T1 with 3 columns: ProductBrand, PurchasedDate, SoldDate. ProductBrand represents the brand of the product. PurchasedDate contains the date (in format yyyy-mm) when we purchased a product and SoldDate contains the date (again in yyyy-mm) when we sold this product. For example:

ProductBrand  PurchasedDate  SoldDate
----------------------------------
Apple          2015-03      2015-05
Samsung        2014-01      2015-03
Sony           2016-02      2016-05 
Sony           2013-01      2013-08 
Apple          2015-05      2015-10 
LG             2011-02      2014-06 
Samsung        2017-02      2017-04 
LG             2016-01      2016-06 
LG             2018-06      2019-01

I would like a table that counts for each brand and for each month (between the years 2010-01 and 2019-12) how many products of this specific brand I had for this particular month in the inventory. Namely, in this example, I have two Apple products. The first one stayed in the inventory between 2015-03 and 2015-06 and the second one stayed in the inventory between 2015-04 and 2015-08. So I expect a table that has:

ProductBrand  YearMonthDate  NitemsInventory
------------  ------------   -------------------
Apple         2010-01               0
Apple         2010-02               0
...            ...                 ... 
Apple         2015-03               1
Apple         2015-04               2
Apple         2015-05               2
Apple         2015-06               2
Apple         2015-07               1
Apple         2015-08               1
Apple         2015-09               0
...            ...                 ...
Apple         2019-12               0

In the same table I would like to have all the brands in the same fashion as Apple. So if we have n different brands and t is the number of months between (2010-01 and 2019-12), the end array will have n times t rows. In other words, I would like see for each month the number of items that I had in the inventory for each brand.

I am using mysql and I suppose I have tried to groupby the column ProductBrand of T1. However, this does not give what I expect.

Upvotes: 1

Views: 70

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

We can do this with some big ugly calendar tables:

SELECT
    t.ProductBrand,
    d.YearMonthDate,
    COUNT(t.ProductBrand) AS NitemsInventory
FROM
(
    SELECT DISTINCT ProductBrand FROM yourTable
) AS p
CROSS JOIN
(
    SELECT '2010-01' AS YearMonthDate UNION ALL
    SELECT '2010-02' UNION ALL
    SELECT '2010-03' UNION ALL
    ...
    SELECT '2015-03' UNION ALL
    SELECT '2015-04' UNION ALL
    ...
    SELECT '2019-12'
) AS d
LEFT JOIN yourTable t
    ON d.YearMonthDate BETWEEN t.PurchasedDate AND t.SoldDate AND
       p.ProductBrand = t.ProductBrand
WHERE
    t.productBrand IS NOT NULL
GROUP BY
    t.ProductBrand,
    d.YearMonthDate;

Here is a demo which shows the query in action:

Demo

Upvotes: 1

Related Questions