sql - MYSQL Subquery on the same table with Group By Clauses... -

having what's simple left/right join mysql... it's bit complex , examples i've seen far can't seem make work have. apologize if bit confusing... i'm going try spell out. basically, there's 3 select queries selecting data same table. there's 4 fields i'm interacting in table called 'order_items' is:

• 'item' - item name, can blank, when need get:
• 'essential_type' - alternate item name
• 'qty' - how many have been ordered of item ordered in particular order. now, if item blank, need count(essential_type) instead of sum(qty) because qty rows null, row needs counted 1
• 'created' - datetime of order creation

---

query #1: gets me name of item , how many sold

select   if(item='', essential_type, item) name,  if(item='', count(essential_type), sum(qty)) qty  order_items  group item, essential_type  order qty 

returns:

name          qty --------------------- 8x10          2345 16x20         340 

---

query #2: gets me number of each item sold year

select   if(item='', essential_type, item) name,  if(item='', count(essential_type), sum(qty)) thisyear  order_items  year(created)=2016  group item, essential_type 

returns:

name          thisyear ----------------------------- 8x10          120 16x20         25 

---

query #3: gets me number of each item sold last year

select   if(item='', essential_type, item) name,  if(item='', count(essential_type), sum(qty)) lastyear  order_items  year(created)=2015  group item, essential_type 

returns:

name          lastyear ----------------------------- 8x10          500 16x20         30 

---

---

so if order_items looks example:

item           essential_type           qty       created --------------------------------------------------------------------- 8x10                                    3         2016-09-01 8x10                                    1         2015-07-21                16x20                    null      2015-08-12                16x20                    null      2016-05-21 

how can combine 3 complex selects 1 select statement using join? final result (based on above sample table):

name          qty         thisyear        lastyear ----------------------------------------------------------- 8x10          4           3               1 16x20         2           1               1 

thanks reading , potential help!

i find logic aggregation awkward (i suspect query simpler using union all). but, can extend query using conditional aggregation:

select (case when item = '' essential_type else item end) name,        (case when item = '' count(essential_type) else sum(qty) end) qty,        (case when item = ''              count(case when year(created) = 2015 essential_type end)              else sum(case when year(created) = 2015 qty end)         end) yr2015,        (case when item = ''              count(case when year(created) = 2016 essential_type end)              else sum(case when year(created) = 2016 qty end)         end) yr2016 order_items group item, essential_type order qty;