Join query not working as expected

Databases
1

I’m trying to get the following query to return the shipping costs associated with each city in a given state. However, it is returning just a single line result from just one city even though there are many other cities.

Any idea what I’m doing wrong?

Thanks!

SELECT 
   AVG(so.latitude) as lat
   ,AVG(so.longitude) as lon
   , SUM( b.net_amount ) as cost
   , so.city
   , so.state
   , so.country
FROM shipments as s
INNER JOIN waypoints as so 
   ON s.origin_id = so.waypoint_id
   AND s.user_id = 1234567
   AND s.shipped_on BETWEEN '2010-01-01' AND '2010-10-31'
INNER JOIN billing as b
   ON b.tracking_number=s.tracking_number
GROUP BY so.city
HAVING so.city<>''
AND so.country='US'
AND so.state='OR'
2

try like this –


SELECT AVG(so.latitude) as lat
     , AVG(so.longitude) as lon
     , SUM( b.net_amount ) as cost
     , so.city
     , so.state
     , so.country
  FROM shipments as s
INNER 
  JOIN billing as b
    ON b.tracking_number = s.tracking_number
INNER 
  JOIN waypoints as so 
    ON so.waypoint_id = s.origin_id
   AND so.country = 'US'
   AND so.state = 'OR'
   AND so.city <> ''
 WHERE s.user_id = 1234567
   AND s.shipped_on BETWEEN '2010-01-01' AND '2010-10-31'
GROUP 
    BY so.city
     , so.state
     , so.country
3

Hi Rudy. Thanks for your quick response as always. I just tried your new query, but it generates the same result as before with just one city.

4

then it’s gotta be a data problem :smiley:

5

maybe your database has only one city in OR in US