2

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.

  • 2
    Could you please add expected result? – James Oct 16 at 7:22
  • 1
    I added a sqlfiddle now. – Erik Eriksson Oct 16 at 7:40
  • @Akina, please see now. I didn't know. Thanks for letting me know, – Erik Eriksson Oct 16 at 7:51
  • 2
    I see structures. But I do NOT see any sample data (INSERT INTO statements)... add please, 2-3 records per table is enough. – Akina Oct 16 at 7:57
  • 1
    Well. Your query now gives 2 records with 2 fields. Please add desured result for that data (one more field? one more record?). – Akina Oct 16 at 8:08
2
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 */
;
  • This is what I had anticipated. I was thinking about UNION all the time. But, is this efficient? Thanks. – Erik Eriksson Oct 16 at 8:33
  • Akina, is this really the best way of doing this? Thx. – Erik Eriksson Oct 16 at 8:43
  • 2
    @ErikEriksson You may group and count in separate subqueries of UNION then sum counts in outer query. Variant added. – Akina Oct 16 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.