INNER JOIN and COUNT in the same query

Question

I am having problems combining INNER JOIN and COUNT in the same query.

Tables:

TABLE STREETS ID | STREET_NAME ------------------------ 1 | Elm street 2 | Some other street 3 | Unknown street 4 | Killer street 5 | Dead-end street TABLE ACCIDENTS_STREETS STREET_ID | ACCIDENT_ID ----------------------- 2 | 4 2 | 7 2 | 2 2 | 1 5 | 3 

I would like to get the name of the street where most of the accidents occurred.

This is for COUNT:

 SELECT TOP 1 COUNT(STREET_ID) AS dangerous_street FROM ACCIDENTS_STREETS GROUP BY STREET_ID ORDER BY dangerous_street DESC 

How to add INNER JOIN to get only street name?

Any advice is appreciated!

Answer 1

Following should work

 SELECT TOP 1 S.STREET_NAME,COUNT(a.*) AS dangerous_street FROM ACCIDENTS_STREETS A inner Join STREET S on S.ID = A.STREET_ID GROUP BY S.STREET_NAME ORDER BY dangerous_street DESC 

Answer 2

try it...

After joining the streets table, you will need to use the aggregation function to get the name in the result set

 SELECT TOP 1 COUNT(STREET_ID) AS dangerous_street , min( STREET_NAME) dangerous_STREET_NAME FROM ACCIDENTS_STREETS acc inner join STREETS str on acc.STREET_ID=str.id GROUP BY STREET_ID ORDER BY dangerous_street DESC