mysql - How can I count the number of stores for each address? -

i have query finds rows duplicate street names:

select a.id, a.street1, a.postal_code, a.fk_countryid address inner join (   select b.street1, b.postal_code, b.fk_countryid   address b   group b.street1, b.postal_code   having count(b.id) > 1 ) duplicate on a.street1 = duplicate.street1  , a.postal_code = duplicate.postal_code , a.fk_countryid = duplicate.fk_countryid order a.street1, a.postal_code  

the result (ignore numstores column):

+-------+-----------------+------------+------+-----+-----------+ | id    | street          | postalcode | city | cc  | numstores | +-------+-----------------+------------+------+-----+-----------+ | 11101 | bogstadveien 36 | 0366       | oslo | no  |     ?     | | 11102 | bogstadveien 36 | 0366       | oslo | no  |     ?     | | 11103 | bogstadveien 36 | 0366       | oslo | no  |     ?     | +---------------------------------------------------+-----------+ 

the goal delete duplicate rows. in order job, need see how many stores using each address. ones 0 count can delete right away. ones 1 or more have "move" address want use before deletion. done manually through gui (js/php).

so question is; how can add number of stores each row?

i found works.

select a.id, a.street1, a.postal_code, a.fk_countryid, count(c.fk_addressid) numstores (...) left join store c on a.id = c.fk_addressid group a.id order a.street1, a.postal_code;  

the result is:

+-------+-----------------+------------+------+-----+-----------+ | id    | street          | postalcode | city | cc  | numstores | +-------+-----------------+------------+------+-----+-----------+ | 11101 | bogstadveien 36 | 0366       | oslo | no  |     0     | | 11102 | bogstadveien 36 | 0366       | oslo | no  |     0     | | 11103 | bogstadveien 36 | 0366       | oslo | no  |     3     | +---------------------------------------------------+-----------+