HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SUM counts from parent and child

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
parentchildsumandfromcounts

Problem

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;


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.