CODEWITHSUNDEEP
mysqlcount
user999568
user999568

Reputation:

MYSQL COUNT(*) for every row

I have two tables in my DB. First is category (categorytbl)

Category
ID | Category

Second is for items (itemstbl)

Items
id | ItemName | CategoryID | Date | Description

Now i need to select and count COUNT(*) all Items from categoryID but I have problem because I can put one item in more than one category

So, when I make list of all categories I need to know how many items I have in every category

category SUV (4 items)

Offroad (43 items)

for example

I have item CAR -> i can put car into SUV, Luxury, Offroad...

thx for helping

Upvotes: 2

Views: 6144

Answers (3)

Matt Fenwick
Matt Fenwick

Reputation: 49095

Addressing the comments, it sounds like you need to refactor your data model because you want a many-to-many (M to N) relationship between items and categories, whereas you have data that's not in 1NF (since it's comma-separated).

This can be accomplished using an intersection table (also known as a junction table: here's a Wikipedia example).

To count the number of categories for each item (that has at least 1 category):

SELECT item_id, count(*) 
FROM junction_table
GROUP BY item_id

And to count the number of items for each category (that has at least 1 item):

SELECT category_id, count(*) 
FROM junction_table
GROUP BY category_id

If you wanted to also include items/categories for which there were no categories/items, you could LEFT JOIN to the appropriate table.

Upvotes: 0

Stefan Gretar
Stefan Gretar

Reputation: 178

Is this what you are looking for?

 SELECT Category, count(*) from Category as c, Items as i
 WHERE c.ID = i.CategoryID
 GROUP BY Category
 ORDER BY count DESC

Upvotes: 0

Johan
Johan

Reputation: 76567

SELECT c.id, c.category, count(c.id) as cat_count
FROM items i
INNER JOIN category c ON (i.category_id = c.id)
GROUP BY c.id

Upvotes: 1

Related Questions