0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.