MySql LEFT JOIN returns only one NULL row from the other table -

i have 2 tables in mysql. table1:

id account_no  1  123  2  124  3  125  4  126  5  127 

table2:

id amount 1  200 1  300 2  400 3  300 2  100 

my desired output is:

account_no total_amount 123  500 124  500 125  300 126  0 127  0 

my query follows

select a.account_no 'account_no', ifnull(sum(b.amount),0) 'total_amount' table1 left join table2 b on a.id = b.id group b.id order a.account_no 

but query i'm getting this

account_no total_amount 123  500 124  500 125  300 126  0 

can me this?

try one.

select a.account_no 'account_no', ifnull(sum(b.amount),0) 'total_amount' table1 left join table2 b on a.id = b.id group a.account_no order a.account_no 

instead of b.bid, has 3 unique value group use a.account_no, have unique account.

let me know query. :)