Re: Combining two queries

[Peter Brawley]

To retrieve rows that are in a and not in b, do an exclusion join ...

select a.accno
from (
  SELECT 
    accno, sender_company ,sender_email 
    , count(*)as 'order_total' 
    , sum(courier_sell-courier_buy) as 'Profit' 
  FROM transactions 
  WHERE Status like 'OK' 
    AND accno >'' 
    AND left,date,4)='2017' 
  Group by accno 
  having count(*)>=52 
  Order by `Profit` DESC 
) as a
left join (
  SELECT 
    accno, sender_company ,sender_email 
    , count(*)as 'order_total' 
    , sum(courier_sell-courier_buy) as 'Profit' 
  FROM transactions 
  WHERE Status like 'OK' 
  AND accno >'' 
  AND left(date,4)='2018' 
  Group by accno 
  having count(*)>=10 
  Order by `Profit` DESC 
) as b using(accno)
where b.accno is null;

Note that ...left(date,4)='2017'... will tend to evaluate faster than ...date > '2017-01-01 00:00' AND date <'2017-12-31 23:59'...