SQL count results on left join -

i'm trying total count of table left join there's multiple of same id. here's example below -

table 1:

+-------------+--------------+------+-----+---------+----------------+ | field       | type         | null | key | default |          | +-------------+--------------+------+-----+---------+----------------+ | id          | int(11)      | no   | pri | null    | auto_increment | | project_id  | int(11)      | no   |     | null    |                | | token       | varchar(32)  | no   |     | null    |                | | email       | varchar(255) | no   |     | null    |                | | status      | char(1)      | no   |     | 0       |                | | permissions | varchar(255) | yes  |     | null    |                | | created     | datetime     | no   |     | null    |                | | modified    | datetime     | no   |     | null    |                | +-------------+--------------+------+-----+---------+----------------+ 

table 2:

+------------+-------------+------+-----+---------+----------------+ | field      | type        | null | key | default |          | +------------+-------------+------+-----+---------+----------------+ | id         | int(11)     | no   | pri | null    | auto_increment | | name       | varchar(32) | no   |     | null    |                | | account_id | int(11)     | no   |     | null    |                | | created    | datetime    | no   |     | null    |                | | modified   | datetime    | no   |     | null    |                | | active     | tinyint(1)  | yes  |     | 1       |                | +------------+-------------+------+-----+---------+----------------+ 

i have statement far -

select account_id, (select count(invitations.id)     invitations invitations.project_id = projects.id) inv_count projects order account_id; 

and here's sample of results:

+------------+-----------+ | account_id | inv_count | +------------+-----------+ |          1 |         0 | |          2 |         2 | |          2 |         0 | |          3 |         4 | |          3 |         0 | |          3 |         4 | |          3 |         0 | |          4 |         6 | |          4 |         3 | |          4 |         3 | |          4 |         5 | |          4 |         3 | |          4 |         9 | |          5 |         6 | |          5 |         0 | |          5 |         4 | |          5 |         2 | |          5 |         2 | 

how account_id show once , sum of inv_count show 1 line? should see -

+------------+-----------+ | account_id | inv_count | +------------+-----------+ |          1 |         0 | |          2 |         2 | |          3 |         8 | 

you need put query in derived table (and name it, tmp) , group account_id:

select account_id,        sum(inv_count) inv_count   ( select account_id,             (select count(invitations.id)             invitations              invitations.project_id = projects.id            ) inv_count     projects    ) tmp group account_id order account_id ; 

to simplify farther, can convert inline subquery left join. way, no derived table needed. i've added aliases , removed order by. mysql implicit order by when have group by it's not needed here (unless want order other expression, different 1 group by):

select      p.account_id,     count(i.id) inv_count       projects p    left join      invitations       on i.project_id = p.id group      p.account_id ;