MySQL, comparison between, tables with not all records common, on 2 time intervals

Question

I want to make comparisons on the volume of sales, turnover and margin of all product sales between 2 time intervals (2017-10-1 to 2017-10-31 ) and (2018-10-1 to 2018-10-31).

for example:

Product Name | Volume 2017 | Volume 2018 | Margin 2017 | Margin 2018 |
     A       |     200     |     100     |     10      |      12     |
     B       |     190     |     120     |     23      |      20     |
     C       |  'no sales' |     100     |  'no sales' |      12     |
     D       |     300     |  'no sales' |     25      |  'no sales' |

My approach is the following:

select
    /* attempt to get either name if null but doesnt work as I join product ID's*/
    if(ifnull(q1.ProductName,false), q2.ProductName , q1.ProductName) as 'Name'
    ,q1.NumofProductsSold as 'Volume 2017'
    ,q2.NumofProductsSold as 'Volume 2018'

    ,format(q1.Margin,2) as 'Margin 2017'
    ,format(q2.Margin,2) as 'Margin 2018'

    ,q1.TotalTurnOver as 'Turnover 2017'
    ,q2.TotalTurnOver as 'Turnover 2018'


from

(   select 

    format(sum(oi.product_quantity),0) as 'NumofProductsSold'
    ,format(sum(oi.turnover),2) as 'TotalTurnOver',
    ,SUM(oi.total_Mark_up) + o_inf.Markup 'Margin'
    ,order_id
     ,ifnull(p.name, 'empty') as 'ProductName'
     ,p.id as 'PID'
     ,op.date as 'date'

     FROM order_items oi JOIN products p ON p.id = op.product_id
                        join product_categories pc on p.id = pc.product_id 
                        join orders o on op.order_id = o.id
                        join order_info o_inf on o.order_number = o_all.orderNumber

     where pc.category_id = 1 -- cat A products only
         AND op.order_status != 'CNL' -- not canceled
           AND op.date >= '2017-09-01' AND op.date <= '2017-10-31'

     group by p.id) as q1

left join 

(  Select

            /* same query BUT for 2018 date range */

 ) as q2

on q1.PID = q2.PID  -- join on product ID

My issue is that if a product doesn't have any sales in either/both the first or second time interval, I get only the rows that correspond on both, so I always "miss" some products if they have sales in one time interval and not the other.

With left/right join I can get correct results only if either set is a subset of the other respectively, i.e if a product ID appears in the right and not the left it doesn't appear with left join. I tried UNION it works BUT it doesn't produce the outcome in specified in the description above. Is there a workaround based on how I designed the Query or should I follow a different approach?

All suggestions-corrections-critique more than welcome! Thanks!

Answer 1

Algorythm:

SELECT product_name,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2017-10-1' AND '2017-10-31' 
                         THEN volume END), 'no sale') AS volume2017,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2018-10-1' AND '2018-10-31' 
                         THEN volume END), 'no sale') AS volume2018,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2017-10-1' AND '2017-10-31' 
                         THEN margin END), 'no sale') AS margin2017,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2018-10-1' AND '2018-10-31' 
                         THEN margin END), 'no sale') AS margin 2018
FROM datatable
GROUP BY product_name
/* WHERE `date` BETWEEN '2017-10-1' AND '2017-10-31'
      OR `date` BETWEEN '2018-10-1' AND '2018-10-31' */