CODEWITHSUNDEEP
sqlcountinner-join
Alfredo
Alfredo

Reputation: 11

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|

Upvotes: 0

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

Presumably you just need to take the count here:

SELECT
    CarrierFirstName + ' ' + carrierlastname AS 'Carrier Name',
    COUNT(*) AS num_customeres
FROM Carrier ca
INNER JOIN Route r
    ON ca.CarrierID = r.CarrierId
INNER JOIN Customer cu
    ON cu.RouteID = r.RouteID
GROUP BY
    CarrierFirstName + ' ' + carrierlastname;

Note that it is not desirable to aggregate the carriers by their names, because two people can have the same first and last name (I even have a double somewhere in the world). Instead, aggregating by some primary key in the Carrier table would be a better strategy.

I introduced aliases into the query to make it easier to read.

Upvotes: 1

Related Questions