CODEWITHSUNDEEP
mysql
user0428
user0428

Reputation: 221

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

The counts of all employees in every state is to be displayed. The query that i have written is:

Select count(distinct a.id)   
from address a   
Group by a.state;

Is there a more optimised solution for this?

There are two 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;

Upvotes: 0

Views: 71

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Your query will not produce the correct output since there is no relation between the employee and address table.

You should add a column to the employee table that will reference the id in the address table:

ALTER TABLE employee
ADD COLUMN address_id INT NOT NULL;

Then, to count the number of employees per state:

SELECT 
    a.state,
    COALESCE(COUNT(e.id), 0) AS employee_count
FROM address a
LEFT JOIN employee e
    ON e.address_id = a.id
GROUP BY a.state

Additionally, you can add a FOREIGN KEY constraint:

ALTER TABLE employee
ADD FOREIGN KEY fk_address(address_id)
REFERENCES address(id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

Upvotes: 1

Related Questions