John John - 1 month ago 8
Java Question

SQL - Get Count

I have a table called COMPANIES that contains info on different companies. How do make a query that gets the company state(COMPANY_STATE) and the company name(COMPANY) count of the most used state (COMPANY_STATE) which has the highest number of different COMPANIES in it. So say Ohio has the most companies in it with 50. How to I query the database to get the state

| COMPANY_STATE | COUNT |
+---------------+-------+
| OH | 50 |
+---------------+-------+


Can i do this via query or do i have to have my java program actually do the work?

Table.sql

CREATE TABLE COMPANIES (
ID INT NOT NULL AUTO_INCREMENT,
COMPANY varchar(255) NOT NULL,
COMPANY_CODE char(10) NOT NULL,
COMPANY_ADDRESS varchar(255),
COMPANY_STATE char(2) NOT NULL,
COMPANY_WORKFORCE INT,
PRIMARY KEY (ID)
)

Answer
SELECT COMPANY_STATE, COUNT(ID) FROM COMPANIES
GROUP BY COMPANY_STATE
ORDER BY COUNT(ID)
LIMIT 1