How to get totals from another table

I have table 1 of Cars and table 2 Trips. Below are the two tables fields

--CARS table
car_id (PK), car_model, car_miles

--TRIPS table
trip_id (PK), trip_destination, trip_date, trip_miles, car_id (FK)

Samle Data

--CARS table
C01, FORD Edge, 100,000
C02, CHEVY Malibu, 89,200
C03, DODGE Avenger, 75,000

--TRIPS table
T01, NYC, 10-jul-2019, 20, C01
T02, Brooklyn, 12-jul-2019, 15, C01
T03, NYC,  09-jul-2019,25, C03

I would like to get the car model with a total number of trips it took like this:

Car_MODEL     | TOTAL TRIPS
--------------|------------
FORD EDGE     | 2
DODGE AVENGER | 1

I tried this query, but I get %s: invalid identifier error

--SQL Query
SELECT SUM(trip_id), car.car_model
FROM trips
LEFT LEFT JOIN car ON trips.car_id = car.car_id
WHERE trips.car_id = car.car_id;

Answer

It doesn’t make sense to aggregate on a field from the left side of a join. It also doesn’t make sense to use sum() on an id. And, your attempted query has other syntax errors as well.

I assume you intend:

SELECT c.car_model, COUNT(t.car_id)
FROM car c LEFT JOIN
     trips t
     ON t.car_id = c.car_id
GROUP BY c.car_model;

Advertisement

Answer