count items in each category
I've the Following Table Schema for table Items
id | item_id | category_id
I've sample data in this table
id | item_id | category_id
1 | 1 | 1
2 | 1 | 2
3 | 2 | 开发者_开发知识库1
4 | 2 | 2
5 | 3 | 1
6 | 1 | 1
Requirement: I need to count all those items which are repeating in each category_id. In the above scenario item_id '1' is repeating in category '1'. I need to count the invalid or repeating item_id against each category_id.
I think this should do it for you:
Select item_Id, category_id
From Table
Group By item_Id, category_id
Having Count(*) > 1
SELECT category_id, item_id, COUNT(*)
FROM table
GROUP BY category_id, item_id
HAVING COUNT(*) > 1
will give you repeating ones. As for 'invalid' you did not define what these are.
精彩评论