6 added 178 characters in body
source | link

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.

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.

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.

5 edited body
source | link

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/f4e181/2http://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.

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/f4e181/2

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.

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.

4 added 9 characters in body
source | link

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/#!9http://sqlfiddle.com/#!9/f4e181/2

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.

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

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.

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/f4e181/2

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.

3 added 178 characters in body
source | link
2 added 91 characters in body
source | link
1
source | link