I have the following tables:

• projects (id)
• items (project_id)
• comments (item_id)

I want to:

1. count all the items in a project
2. count all the comments in a project.

For #1, I am doing the following:

SELECT 
    p.*,
    (SELECT COUNT(*) FROM items WHERE project_id=p.id) items_count,
FROM projects p

But I can't figure out how to go one level deeper and count the comments. Any help would be appreciated.

CodePudding user response：

Assuming that id is the primary key of projects and both items and comments have primary keys, say id, you can do a LEFT join of projects to the other tables and aggregate:

SELECT p.*,
       COUNT(DISTINCT i.id) items_count,
       COUNT(c.id) comments_count
FROM projects p
LEFT JOIN items i ON i.project_id = p.id
LEFT JOIN comments c ON c.item_id = i.id
GROUP BY p.id;

See a simplified demo.