below are the two tables:
- There Are Two Tables Region Tables And Accident Tables I Wrote A Query To Join The Two Tables Based Off Of State And S 1 (48.52 KiB) Viewed 20 times
Select region, rank() over(partition by region order bysum(number_of_accidents) as rank, region_tbl.state,sum(number_of_accident)
From region_tbl
Inner join car_accident_tbl
On region_tbl.state_id =car_accident_tbl.state_id
And region_tbl.state =car_accident_tbl.state
Where state != ‘Alaska’
Group by region, region_tbl.state
this query essentially list all the regions and ranksthem from highest state to least per region. Work off of thisquery!!!
for this questions the goal is
In sql, using rank or partition, write a query thats aggregatesthe top three states that had the most amount of accident per eachregion, after the top three add the rest of the states togetherwhere there were accidents and label as other. so for example forthe west region groupings, add all the entries for each state andenter the top 3 states, after that add all the other states entriestogether and label as other. Remember the goal is to know which top3 states had the most accidents, and besides the top 3 how manyaccidents were there for all states combined(other) per region. Soeach region(west,north,east,south) should have top 3 states andother associated with it
this is what the output(final result) of the query shouldbe:
- There Are Two Tables Region Tables And Accident Tables I Wrote A Query To Join The Two Tables Based Off Of State And S 2 (50.04 KiB) Viewed 20 times
Region WEST WEST WEST Other SOUTH SOUTH SOUTH Other EAST EAST EAST Other NORTH NORTH NORTH Other Rank 1 UT 2 NW 3 AZ State 'other_rank' other 1 TX 2 CA 3 NJ 'other_rank' other 1 HW 2 GA 3 TN 'other_rank' 'other_rank' other 1 ta 2 tv 3 cd other Sum_amount_accidents 40 30 25 55 56 50 35 80 30 20 10 50 10 5 1 50