CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
TuriCho
TuriCho

Reputation: 41

Calculate aggregated sum from two tables

From the following tables I need to determine which city receives the most pollution from buses.

routes:

route_id | departure_city | destination_city | bus_type | times_per_day
 1         2                1                  1          4
 2         1                3                  2          2
 3         3                1                  2          1
 4         1                2                  1          5
 5         1                3                  1          3

bustypes:

bus_type_id | pollution_output
 1             3
 2             7

For example city 2 is exposed to bus_type 1 four times a day (route_id 1) and bus_type 1 five times a day (route_id 4) giving a pollution output of 27 per day. But I basically need to calculate this for all the cities and return the one with maximum pollution, how do I do that?

Upvotes: 0

Views: 65

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658947

SELECT city, sum(pollution) AS total_pollution
FROM (
   SELECT r.depature_city AS city
         ,b.pollution_output * r.times_per_day AS pollution
   FROM   routes   r
   JOIN   bustypes b ON b.bus_type_id = r.bus_type

   UNION ALL
   SELECT r.destination_city
         ,b.pollution_output * r.times_per_day
   FROM   routes   r
   JOIN   bustypes b ON b.bus_type_id = r.bus_type
   ) AS sub
GROUP  BY city

Upvotes: 1

Related Questions