mysql - I need to write a query to display list of counts of employee in every state -

the counts of employees in every state displayed. query have written is:

select count(distinct a.id)    address    group a.state; 

is there more optimised solution this?

there 2 tables

create table `address` (   `id` int(11) not null auto_increment,   `city` varchar(255) not null,   `state` varchar(255) not null,   primary key (`id`) ) engine=innodb auto_increment=4832 default charset=utf8;  create table `employee` (   `id` int(11) not null auto_increment,   `fname` varchar(255) not null,   `lname` varchar(255) not null,   `email` varchar(255) not null,   `title` varchar(255) not null,   `dob` datetime not null,   primary key (`id`) ) engine=innodb auto_increment=58993 default charset=utf8; 

your query not produce correct output since there no relation between employee , address table.

you should add column employee table reference id in address table:

alter table employee add column address_id int not null; 

then, count number of employees per state:

select      a.state,     coalesce(count(e.id), 0) employee_count address left join employee e     on e.address_id = a.id group a.state 

---

additionally, can add foreign key constraint:

alter table employee add foreign key fk_address(address_id) references address(id) on delete no action on update no action;