patternsqlMinor
SUM counts from parent and child
Viewed 0 times
parentchildsumandfromcounts
Problem
With this SQL-query, I am able to obtain the
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.
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;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.
Solution
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 /
;
Context
StackExchange Database Administrators Q#251203, answer score: 3
Revisions (0)
No revisions yet.