Count two different columns from two different tables and throw into a single variable

Question

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!

Answer 1

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;

Answer 2

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.