scratch.sql

SELECT
        d.contact_name AS "Drop Name",
        CONCAT(d.address_1, ', ', d.city, ', ', states.abbrev) AS "Drop Location",
        to_char(avg(drop_route_trip.cached_ordered_weight),'99999999999999999D99') 
          AS "Avg Weight for Drop",
        to_char(sum(avg(drop_route_trip.cached_ordered_weight))
           over(order by MAX(drop_route_trip.cached_sequence)), '99999999999999999D99') AS "Load Total"
FROM routes_route AS route
LEFT JOIN routes_routetrip AS route_trip
     ON route.id = route_trip.route_id
LEFT JOIN drops_droppointtrip AS drop_route_trip
     ON drop_route_trip.route_trip_id = route_trip.id
INNER JOIN drops_droppoint AS d
     ON drop_route_trip.drop_point_id = d.id
INNER JOIN countries_usstate AS states
     ON d.state_id = states.id
WHERE route.code = 'J2' AND
      route_trip.departure BETWEEN '2012-11-01' AND '2013-02-28' AND
      route_trip.status = 4 --Shipped 
GROUP BY d.id,  states.abbrev
HAVING avg(drop_route_trip.cached_ordered_weight) > 0
ORDER BY MAX(drop_route_trip.cached_sequence);