How can I find state name which has the most vehicle accidents in the database?

Question

I have a table "ACC_DATA" that has the following columns: ACCIDENT_ID, STATE_CODE, ACC/DATE and a second table "US_STATES" which has the following columns: STATE_CODE and STATE_NAME

Each accident that happened is given an ID and the State in which it occurred, in the "ACC_DATA" table. How can I use SQL to find the name of the State which had the most accidents?

This is all that I have so far:

SELECT AD.STATE_CODE, COUNT(*) AS FREQUENCY
FROM ACC_DATA AD, US_STATES US
GROUP BY AD.STATE_CODE
ORDER BY COUNT(*) DESC

But this only gives a table of State codes with number of accidents in each. I am not quite sure how to join the two tables to produce the state name with a single max value.

Answer 1

A join should do, and then select the state name instead.

SELECT US.STATE_NAME, COUNT(*) AS FREQUENCY
FROM ACC_DATA AD, US_STATES US
WHERE AD.STATE_CODE = US.STATE_CODE
GROUP BY US.STATE_NAME
ORDER BY COUNT(*) DESC