Financial year data with default future months value mysql query

Question

I am generating chart for financial year 2018-2019 (April to March) and for that I am fetching average total of sales of each month. I want to get data for entire financial year even if the data is not available for future month because in graph i have used combined bar graph so this is 1 part of it and other will be added similarly. Using below query I am only getting average total for the months of which data is inserted in the database but I need all the financial year data like below.

Required Response -

"sales" : [
    {
        "yearMonth" : "2018-04",
        "netTotal" : "154800.0"
    },
    {
        "yearMonth" : "2018-05",
        "netTotal" : "450410.0"
    },
    {
        "yearMonth" : "2018-06",
        "netTotal" : "124780.0"
    },
    {
        "yearMonth" : "2018-07",
        "netTotal" : "98750.0"
    },
    {
        "yearMonth" : "2018-08",
        "netTotal" : "0.0"
    },
    {
        "yearMonth" : "2018-09",
        "netTotal" : "0.0"
    },
    and so on till (March) 31-03-2019.....
]

Query1 -

SELECT DATE_FORMAT(quotationDate, '%Y-%m') AS yearMonth, AVG(netTotal) as netTotal FROM
quotation_master WHERE companyId = '1' AND fiscalYear = '2018-2019' GROUP BY yearMonth

Query2 -

SELECT meses.month, CONCAT(YEAR(CURDATE()), '-', meses.month) as yearMonth, IF(AVG(netTotal) IS NULL, 0, AVG(netTotal)) as netTotal FROM(
        SELECT 01 AS MONTH
        UNION SELECT 02 AS MONTH
        UNION SELECT 03 AS MONTH
        UNION SELECT 04 AS MONTH
        UNION SELECT 05 AS MONTH
        UNION SELECT 06 AS MONTH
        UNION SELECT 07 AS MONTH
        UNION SELECT 08 AS MONTH
        UNION SELECT 09 AS MONTH
        UNION SELECT 10 AS MONTH
        UNION SELECT 11 AS MONTH
        UNION SELECT 12 AS MONTH
) as meses
LEFT JOIN quotation_master QM ON meses.month = MONTH(quotationDate) 
WHERE QM.companyId = '1' AND QM.fiscalYear = '2018-2019' GROUP BY yearMonth

Query Output - As data till July month is available, it gives me that data only and not for other months like August, Sep, Oct and so on. I know that data is not there in the database for future months and that's why i like to get 0.0 as default value for future months.

"sales" : [
        {
            "yearMonth" : "2018-04",
            "netTotal" : "154800.0"
        },
        {
            "yearMonth" : "2018-05",
            "netTotal" : "450410.0"
        },
        {
            "yearMonth" : "2018-06",
            "netTotal" : "124780.0"
        },
        {
            "yearMonth" : "2018-07",
            "netTotal" : "98750.0"
        }
    ]

Will B. · Accepted Answer

As mentioned, the issue is within your WHERE criteria. Since your WHERE applies the criteria to the initial FROM table and filters out the returned dataset.

To resolve the issue simply replace WHERE with AND on your JOIN ON criteria, to limit the filtered dataset of only the joined table data.

Example: http://sqlfiddle.com/#!9/3181f0/2

Table Schema and Data:

 CREATE TABLE quotation_master 
        (`companyId` int, `quotationDate` datetime, `netTotal` int, `fiscalYear` varchar(10))
    ;

INSERT INTO quotation_master 
    (`companyId`, `quotationDate`, `netTotal`, `fiscalYear`)
VALUES
    (1, '2018-04-01 00:00:00', 154800.0, '2018-2019'),
    (1, '2018-04-02 00:00:00', 154800.0, '2018-2019'),
    (1, '2018-05-01 00:00:00', 450410.0, '2018-2019'),
    (1, '2018-05-02 00:00:00', 450410.0, '2018-2019'),
    (1, '2018-06-01 00:00:00', 124780.0, '2018-2019'),
    (1, '2018-06-02 00:00:00', 124780.0, '2018-2019'),
    (1, '2018-07-01 00:00:00', 98750.0, '2018-2019'),
    (1, '2018-07-02 00:00:00', 98750.0, '2018-2019')
;

Query:

SELECT 
  meses.month, 
  CONCAT(YEAR(CURDATE()), '-', meses.month) as yearMonth, 
  COALESCE(AVG(netTotal), 0) as netTotal 
FROM(
        SELECT 01 AS MONTH
        UNION SELECT 02 AS MONTH
        UNION SELECT 03 AS MONTH
        UNION SELECT 04 AS MONTH
        UNION SELECT 05 AS MONTH
        UNION SELECT 06 AS MONTH
        UNION SELECT 07 AS MONTH
        UNION SELECT 08 AS MONTH
        UNION SELECT 09 AS MONTH
        UNION SELECT 10 AS MONTH
        UNION SELECT 11 AS MONTH
        UNION SELECT 12 AS MONTH
) as meses
LEFT JOIN quotation_master QM 
ON meses.month = MONTH(quotationDate) 
AND QM.companyId = '1' 
AND QM.fiscalYear = '2018-2019' 
GROUP BY yearMonth;

Results

| month | yearMonth | netTotal |
|-------|-----------|----------|
|     1 |    2018-1 |        0 |
|    10 |   2018-10 |        0 |
|    11 |   2018-11 |        0 |
|    12 |   2018-12 |        0 |
|     2 |    2018-2 |        0 |
|     3 |    2018-3 |        0 |
|     4 |    2018-4 |   154800 |
|     5 |    2018-5 |   450410 |
|     6 |    2018-6 |   124780 |
|     7 |    2018-7 |    98750 |
|     8 |    2018-8 |        0 |
|     9 |    2018-9 |        0 |

Note: As mentioned in my comment, be sure to test the desired results of COALESCE(AVG(netTotal), 0) as opposed to AVG(COALESCE(netTotal, 0)), as MySQL skips the NULL aggregate columns and would not account for rows that exist but are NULL.

Financial year data with default future months value mysql query

Answers (2)

Related Questions