I've got a a table of records which has following fields
| id | status | parent_id | title |
|-----------------|-----------|-----------|
| 1 | 'active' | null | test one |
| 2 | 'active' | null | test one |
| 3 | 'active' | 1 | test one |
| 4 | 'active' | 1 | test one |
| 5 | 'closed' | 1 | test one |
| 6 | 'cancelled'| 2 | test two |
| 7 | 'closed' | 2 | test three|
column parent_id points to a parent record in the same table, i.e. records with ids 1 and2 are parent records since the are records that have them as parent but they themselves don't have parent and also cannot have since inheritance is only 1 level deep.
I need the best way sql query that will return me the following result :
|pid | active_children_count | closed_children_count | cancelled_chilrent_count |
|----------------------------|-----------------------|--------------------------|
| 1 | 3 | 0 | 0 |
| 2 | 0 | 1 | 1 |
basically I need to return count of all child ads with a specific status grouped by parent.
Right now I'm using inner select and count
SELECT id AS pid,
(SELECT COUNT(*) FROM records WHERE parent_id = pid AND status = 'active') AS active_children_count,
(SELECT COUNT(*) FROM records WHERE parent_id = pid AND status = 'closed') AS closed_children_count,
(SELECT COUNT(*) FROM records WHERE parent_id = pid AND status = 'cancelled') AS cancelled_children_count
FROM records WHERE pid IN (1,2);
but been running into performance problems with several million records. Is there a better way?
