I have two tables in my project: "cats" and "items". Every single item in "items" table has the value "item_cat" (column), which represents the ID of actual category, where that item is assigned to. And every single category in "cats" table has its unique ID.

When I add a new item, I can choose the category ID which can only be the one of categories from "cats" table: "music" (id=1), "books" (id=2) or "movies" (id=3). Items stored: "Pulp fiction" (id=1), "Mein kampf" (id=2), "Gone with the wind" (id=3), "Les miserables" (id=4), "The Holy Bible" (id=5) and "The best of Tina Turner" (id=6). Items 1, 3 and 4 belong to category 3, items 2 and 5 belong to category 2, and item 6 belongs to category 1 (I hope I'm still understandable ;) ).

Now I want to count how many items are assigned to every category. Is it possible to use "SELECT COUNT" in some combination, to get such a result as an output: cat1 = 1 (item), cat2 = 2 (items), cat3 = 3 (items)? How to do this?

Thank you in advance for your help!

2 Answers 2

SELECT item_cat, COUNT(*)
    FROM items
    GROUP BY item_cat;

(I see no need for a CASE.)

Now, assuming that the table cats has a name column, you might want:

SELECT ( SELECT name FROM cats WHERE id = i.item_cats ) AS "Category",
       COUNT(*) AS "Number of items in that Category"
    FROM items
    GROUP BY item_cat;
  • Thank you Rick. Your code works and shows a number of existing items related to a categories. The only limitation is that it omits the categories where no items are assigned to. Anyway, this is a really simple code which I will definitely use somewhere else in my code. Thank you!
    – Vogelek23
    Commented Aug 12, 2019 at 18:09
  • Hey Rick, there is the problem with a 2nd query - it fails with an "unknown column i.item_cats" error. After changing it to "item_cat" (without "i." prefix), the query passes and gives a correct results.
    – Vogelek23
    Commented Aug 12, 2019 at 18:16

I found myself a solution. Here is the query:

select i.item_cat, count(*) total, sum(case when i.item_cat = c.cat_id then 1 else 0 end) SumCat FROM `items` i left join `cats` c on (i.item_cat = c.cat_id) group by i.item_cat 

It outputs:

item_cat | total | SumCat
    1    |   1   |   1
    2    |   2   |   2
    3    |   3   |   3


EDIT: Found a better and (IMO) more efficient solution, which also outputs an empty categories (where no items are assigned to). Firstly, I have added a two more categories: "games" (id=4) and "cooking" (id=5), but did only insert a single item into a "cooking" category, leaving "games" category with no items assigned. Then, the query below did a really nice piece of work:

select c.cat_id, sum(case when i.item_cat = c.cat_id then 1 else 0 end) total FROM `cats` c left join `items` i on (c.cat_id = i.item_cat) group by cat_id 

This will output:

item_cat | total
    1    |   1
    2    |   2
    3    |   3
    4    |   0
    5    |   1

So the solution above suits my needs completely and does exactly what I want it to do.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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