COUNT Total items from main and subcategory

Databases
1

I wrote the following sql:

SELECT parent.name, COUNT(DISTINCT i.item_id) AS total
FROM categories parent
LEFT JOIN categories node ON parent.parent = node.parent
LEFT JOIN item_category ic ON node.category_id = ic.category_id
LEFT JOIN items i ON ic.item_id = i.item_id
GROUP BY parent.name;

My running SQLFiddle:

I have a self-referencing table and a linking table, so three tables.

2

And the desired output?

1 Like
3

The name of main category with total count of self + sub . Currently, my SQL cannot take into account the items linked with main via the linking table. I have tried, UNION ALL , but maybe I failed, because I couldn’t get it to output the name of the main category + total count .

4

Even more specific?

5

I think what sibertius is trying to say is… forget your existing query.
In english, write out what information you’re trying to get from the database.

6

The output I am getting, both using this query and the previous one is as follows:

-----------------------------
|     name        |  total  |
-----------------------------
|    Pepsi        |    0    |
|    Pepsi Zero   |    1    |
-----------------------------

This is wrong, because in the linking table you can see that two items are linking to the ID of the first category, parent. Which has NULL in parent value. This is a self-referencing table. See my SQLfiddle again.

My desired output is:

----------------------------
|     name    |   total   |
---------------------------
|    Pepsi     |    3     |
----------------------------

So, the Pepsi zero which is a subcategory of Pepsi, must not be shown, only Pepsi which is the main category + the total count of items from either Pepsi or Pepsi Zero)

My running SQLFiddle:

7

So what you want is a total number of items in a specific category+its subcategories.

SELECT COUNT(DISTINCT items.item_id) 
FROM items
LEFT JOIN item_category ic ON ic.category_id = items.item_id
WHERE ic.category_id = 1 OR ic.parent = 1
8

Or i could read the schema properly and add the extra table in. Because brain.

SELECT COUNT(DISTINCT items.item_id) 
FROM items
LEFT JOIN item_category ic ON ic.category_id = items.item_id
LEFT JOIN categories c ON ic.category_id = c.category_id
WHERE c.category_id = 1 OR c.parent = 1
9

NO! I did solve it, but I am curious, how do you get only one name to output?

10

you’re going to have to be more specific. you’ve got multiple things with names in your query.

1 Like
11

http://sqlfiddle.com/#!9/75bd57/6 ← see this SQL code.

I am getting, Pepsi and Pepsi Max, but Pepsi Max is a subcategory of Pepsi, thus, it should show Pepsi and total count = 3, not like it showing at the moment.

Let me know if you see the result output from the SQL fiddle link.

12

So you didn’t solve it.

If you’re trying to roll up all of your stuff, then you will need to Ifnull test (because it’s a binary test) roll them up.

Single Level Adjacency Rollup-and-Total:

SELECT co.name, COUNT(DISTINCT iq.item_id) FROM
(SELECT i.item_id,
IFNULL(c.parent,c.category_id) AS cat
FROM items i
LEFT JOIN item_category ic ON i.item_id = ic.item_id
LEFT JOIN categories c ON ic.category_id = c.category_id
) iq
LEFT JOIN categories co ON co.category_id = iq.cat
GROUP BY co.name
1 Like
13

OK, so I thought I did SOLVE it, but I was wrong. I am trying to understand, what do you mean by “Single Level Adjacency Rollup-and-Total”?

14

I’m saying that this code wont work if your subcategories have subcategories (multiple-level adjacency).

1 Like
15

Hmm. So that would mean, expanding and adding more LEFT JOINS? Right?

16

Essentially you’d have to run the subquery as many levels deep as your data goes. Which is why at that point you pretty much abandon the idea of adjacency models and move to something like a nested set model.

17
18

https://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/

19

i really hate showing up to a thread that’s only a couple hours old and finding a couple dozen posts all over the place as though nobody is in charge of what’s going on with the design

the adjacency model is just fine for easily a dozen levels

all you gotta do, and it’s a lot easier than it looks, is write as many self-joins as levels you want to descend

i would link to my Categories and Subcategories article again, but i have a feeling no one is going to be happy if i do

one final word – please remember that X left join Y along with a WHERE condition on a column of Y will result in an inner join, so why not code it that way from the get-go

1 Like
20

ehhh. The query lengths are what make me cringe :stuck_out_tongue: From a database load perspective, sure, but I’d get anxious with a dozen self-joins to get a single piece of information.