I’m trying to build a stacked column chart using the Google Charts API pulling data from MySQL.
There are 2 values: Sales and Quotes. The Sales calculate the duration from the Start / Finish dates and divide the value by the duration to give a monthly figure.
The quotes does the same, but the value is multiplied by the probability to bring it down.
In the table, Sales has a status of ‘Work Order’ and Quotes has a status of ‘Quote’.

I’ve had everything working for a single data series for Quotes using the following MySQL Query:

WITH RECURSIVE dates(date) AS (
            SELECT CONCAT(LEFT(CURRENT_DATE, 7), '-01') AS date
            UNION ALL
            SELECT DATE_ADD(date, INTERVAL 1 MONTH) 
            FROM dates
            WHERE date < DATE_ADD(current_date(), INTERVAL 12 MONTH)
            )
            SELECT 
                sum(projects.sellValue * projects_meta.probability / PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM tentativeFinish), EXTRACT(YEAR_MONTH FROM tentativeStart))) AS quotesM,
                projects.status,
                dates.date
            FROM projects_meta LEFT JOIN projects ON projects.projectNo = projects_meta.PID
                        
            CROSS JOIN dates
            WHERE dates.date >= tentativeStart AND dates.date < tentativeFinish AND projects.status IN ('Quote') GROUP BY dates.date;

I’m now trying to stack this graph with another series for Sales.
I tried this, but I’m struggling to see how to separate the IN or WHERE condition as the result is stacking the full value of both Quotes and Work Orders on top of the Quote * Probability series due to the projects.status IN ('Work Order','Quote').

WITH RECURSIVE dates(date) AS (
            SELECT CONCAT(LEFT(CURRENT_DATE, 7), '-01') AS date
            UNION ALL
            SELECT DATE_ADD(date, INTERVAL 1 MONTH) 
            FROM dates
            WHERE date < DATE_ADD(current_date(), INTERVAL 12 MONTH)
            )
            SELECT 
                sum(projects.sellValue * projects_meta.probability / PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM tentativeFinish), EXTRACT(YEAR_MONTH FROM tentativeStart))) AS quotesM,
                sum(projects.sellValue / PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM tentativeFinish), EXTRACT(YEAR_MONTH FROM tentativeStart))) AS salesM,
                projects.status,
                dates.date
            FROM projects_meta LEFT JOIN projects ON projects.projectNo = projects_meta.PID
                        
            CROSS JOIN dates
            WHERE dates.date >= tentativeStart AND dates.date < tentativeFinish AND projects.status IN ('Work Order','Quote') GROUP BY dates.date;

So what I’m trying to do, is only sum all the values where status is Work Order, then sum all the (value * probability) where status is Quote.

I’ve tried to look at using UNION but I think I’m having difficulty because of the CROSS JOIN with the recursive Dates.

There should a way for me to run 2 separate queries and join them in PHP, but I’m sure there is a way to do this within the DB Query, but I’m not sure how and would really appreciate some guidance on where I need to be looking.

Here is how the chart currently appears with the bottom query: