计算每个类别sql php的项目

Question

using sql and php.

I have the following table schema

id | item_id | category_id

I've sample data in this table

id  | item_id | category_id

1   | 1525       | shoes
2   | 2565       | shoes
3   | 2558       | shirts
4   | 2885       | shirts
5   | 3545       | shirts
6   | 1885       | belts

Requirement: I need to count how many items are in each category, and echo the count and item_ids

for example "category shoes has 2 items - 1525,2565" I need to do this for all categories.

Answer 1

SELECT category_id, COUNT(*) as num_items, GROUP_CONCAT(item_id) AS items
FROM YourTable
GROUP BY category_id