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;
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.