Mysql – SUM counts from parent and child

MySQL

With this SQL-query, I am able to obtain the count(*) of items in subcategory, but I want to be able to SUM total items, from both main (parent) category and subcategory (child), how can this be achieved? 

SELECT parent.name,
COUNT(sub.item_id) AS total
FROM categories parent
LEFT JOIN subcategories child ON parent.category_id = child.category_id
LEFT JOIN item_subcategory sub ON child.subcategory_id = sub.subcategory_id
LEFT JOIN items i ON sub.item_id = i.item_id
GROUP BY parent.name;

enter image description here

EDIT: http://sqlfiddle.com/#!9/7ec5b8/1

Note, I am using the latest MySQL on my PC, but the one supported there is 5.6 at highest, for some reason I keep getting syntax error.

EDIT2: This QUERY currently COUNTs ITEMS in subcategories, is IT possible to within the same query count for items in main categories and then SUM these two counts.

Best Answer

SELECT name, COUNT(item_id) 
FROM ( SELECT c.name, i.item_id
       FROM categories c
       LEFT JOIN item_category ic ON ic.category_id = c.category_id
       LEFT JOIN items i ON ic.item_id = i.item_id

     UNION ALL

       SELECT c.name, i.item_id
       FROM categories c
       LEFT JOIN subcategories sc ON sc.category_id = c.category_id
       LEFT JOIN item_subcategory isc ON isc.subcategory_id = sc.subcategory_id
       LEFT JOIN items i ON isc.item_id = i.item_id
     ) total
GROUP BY name /* WITH ROLLUP */
;

or 

SELECT name, SUM(cnt)
FROM ( SELECT c.name, COUNT(i.item_id) cnt
       FROM categories c
       LEFT JOIN item_category ic ON ic.category_id = c.category_id
       LEFT JOIN items i ON ic.item_id = i.item_id
       GROUP BY c.name

     UNION ALL

       SELECT c.name, COUNT(i.item_id)
       FROM categories c
       LEFT JOIN subcategories sc ON sc.category_id = c.category_id
       LEFT JOIN item_subcategory isc ON isc.subcategory_id = sc.subcategory_id
       LEFT JOIN items i ON isc.item_id = i.item_id
       GROUP BY c.name
     ) total
GROUP BY name /* WITH ROLLUP */
;
Related Question