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

aggregatejoin;mysql-5.7union

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!

Best Answer

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' */

Related Solutions

Mysql – Slow performance of MySQL Join Query

Please provide SHOW CREATE TABLE; the explain is useless without it.

OR is a performance killer in many contexts.

( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR ( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ...

Turn that into

JOIN ( SELECT id FROM p WHERE 
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR 
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ... ) x ON x.id = foo.id

Also needed (on p):

INDEX(pricelist, iln, id)

(With the CREATEs, I could be more specific.)

The idea behind this "trick" is to move the costly work of the OR into a subquery that returns the necessary ids. Plus the INDEX makes it so that it can do all that work in the INDEX.

Join for between multiple tables without common fields

Which make of SQL are you using? I am most familiar with Microsoft SQL Server. Syntactically, you need to move the WHERE after all of the JOIN statements. (I appended the tablename L to its references for consistency.)

select * from L
left outer join ER on ER.childid=L.id
left outer join E on E.id=ER.parentid
left outer join M on M.id=ER.parentid
where L.xpos > 5

This is legal syntax. The next question is what results you want. I think possibly so, although you did not show a sample of results. Perhaps you can play with the data a bit to decide what you need.

CREATE table E (id INT, title varchar(50), [datetime] DateTime);
CREATE table L (id INT, place varchar(50), xpos float, ypos float);
CREATE table M (id INT, title varchar(50), [datetime] DateTime, contact varchar(50), agenda varchar(50));
CREATE table ER (parentid INT, childid INT, parenttype INT, childtype INT);

INSERT INTO L VALUES (1, 'Boston',123.234, 23.234);
INSERT INTO L VALUES (3, 'Miami', 456.234, 78.234);
INSERT INTO ER VALUES (100,1, 5, 4);
INSERT INTO ER VALUES (200,2, 5, 4);
INSERT INTO ER VALUES (200,3, 5, 4);
INSERT INTO ER VALUES (100,4, 5, 4);
INSERT INTO E VALUES (100, 'Able','2014/01/02');
INSERT INTO E VALUES (2, 'Baker','2014/01/11');
INSERT INTO M VALUES (100, 'Love', '2014/04/04', 'George', 'None');
INSERT INTO M VALUES (200, 'Fury', '2014/05/05', 'Bernard', 'Something big');


SELECT * FROM L 
    LEFT OUTER JOIN ER on ER.childid = L.id
    LEFT OUTER JOIN E on E.id= ER.parentid
    LEFT OUTER JOIN M on M.id= ER.parentid
    WHERE L.xpos > 5

DROP TABLE E
DROP TABLE L
DROP TABLE M
DROP TABLE ER

Best Answer

Related Solutions

Mysql – Slow performance of MySQL Join Query

Join for between multiple tables without common fields

Related Question