>Hi all -
>I am still trying to get the SQL group by clause down. Can someone help me with this?
>The table contains a record for each accident at intersections in the city. Fields are an intersection identifier INT_ID, the 2 cross streets STREET1 and STREET2, injury severity INJURY_SEV coded 1 through 6. I want to obtain 1 record per intersection containing the total number of crashes at the intersection plus the total number for each of the 6 severity codes. This works for the total but I can't figure out how to add the six severity codes.
>
>
>select int_id, street1, street2, count(int_id) as crashes;
> from crash1 ;
> group by int_id, street1, street2 ;
> order by crashes desc, int_id
>
>
>Thanks
Don,
You need to add a separate sum for each of the cases, e.g.
select int_id, street1, street2, count(int_id) as crashes, ;
sum(iif(Injury_Sev =1, 1,0)) as Total_Sev_1, ;
sum(iif(Injury_Sev =2, 1,0)) as Total_Sev_2, ;
sum(iif(Injury_Sev =3, 1,0)) as Total_Sev_3, ;
sum(iif(Injury_Sev =4 1,0)) as Total_Sev_4, ;
sum(iif(Injury_Sev =5, 1,0)) as Total_Sev_5, ;
sum(iif(Injury_Sev =6, 1,0)) as Total_Sev_6 ;
from crash1 ;
group by int_id, street1, street2 ;
order by crashes desc, int_id
If it's not broken, fix it until it is.
My Blog