CODEWITHSUNDEEP
mysqlrdbms
Aminator
Aminator

Reputation: 160

Getting error in SQL query using INNER JOIN

I'm still learning the MySQl. This is the relational DBMS :

CUSTOMER (CustID, CustName, AnnualRevenue)
TRUCK (TruckNumber, DriverName)
CITY (CityName, Population)
SHIPMENT (ShipmentNumber, CustID, Weight, Year, TruckNumber, CityName)

Now, I have to formulate for these two queries:

  1. Total weight of shipments per year for each city.
  2. Drivers who drove shipments to London but not Paris.

These are the queries i have came up with: 1.

select sum(s.weight), s.year , c.city
from shipment s, city c
INNER JOIN CITY
on s.CityName = c.CityName

Upvotes: 1

Views: 124

Answers (1)

Israel B
Israel B

Reputation: 171

You are mixing and old way to JOIN table (which you should avoid because the joining columns are not explicitly stated and it is confusing for others):

FROM shipment s, city c

You should group columns in the select that are not aggregated (year, city). Also it is better to use an alias for the aggregated column (AS total_weight)

select sum(s.weight) AS total_weight, s.year , c.city
from shipment s
INNER JOIN CITY as c
on s.CityName = c.CityName
GROUP BY s.year, c.city

Try to solve the second query and come back if you have a problem.

Upvotes: 2

Related Questions