Search code examples
sqlcountinner-join

SQL Inner join into counting distinct

I have three tables: Customer - Region - Carrier

I need to find out how many customers each carrier has. 

select CarrierFirstName + ' ' + carrierlastname ' Carrier Name', CustomerID
from Carrier
inner join Route ON Carrier.CarrierID = Route.CarrierId
inner join Customer ON Customer.RouteID = Route.RouteID
group by carrier.CarrierFirstName, CarrierLastName, CustomerID

This gives me carrier names several times and each distinct customerID, whereas i need names followed by the TOTAL number of customers associated with each name

Joe Johnson | 15|
Carrie Lan  | 12|
Solution

  • You need to fix the group by. This also uses table aliases to simplify the query:

    select (c.CarrierFirstName + ' ' + c.carrierlastname) as carrier_name,
       count(*)
from Carrier c inner join
     Route r
     on c.CarrierID = r.CarrierId inner join
     Customer cu
     on cu.RouteID = r.RouteID
group by c.CarrierFirstName, c.CarrierLastName