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.