CODEWITHSUNDEEP
mysqlsqljoin
Sibghat Khan
Sibghat Khan

Reputation: 147

Join with multiple columns from two tables

I have two tables. First table is named price_changes where as the other is named as area. The structure of price_changes is : 

**listing_id**  **old_price** **new_price** **change_date**
240509            180999        100234        2016-03-30
230599            165789        189760        2017-06-12
245678            123456        176432        2016-12-08

here the listing_id,old_price,new_price are integers whereas change_date is in text.

The next table which is area has a structure like this:

**listing_id** **built_area**, **used_area** 
240509           340             0
230599           0               789
245678           125             175

here the listing_id, built_area, used_area are all integer values.

The Sql query that I want to make and unable do so is this:

calculate the average square meter price of properties with an increased price in 2016 that have built up or used area > 200. Here the average square meter price would mean summing both built_area and used_area to form a new column called total_area and for price I have to use the increased_price column.

I tried to do it with nested Queries but was not successful. The nested query that I came up was

SELECT listing_id FROM fast_course_reg.price changes where new_price > old_price and change_date like '2016%' and listing_id in (select listing_id from area where built_area > 200 or used_area > 200);

but the problem is nested query doesn't allow to add more than one column in the nested section. With Join, I don't know how do to that since inner join doesn't allow to select multiple columns from both tables.

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

To get the properties whose price increased in 2016:

select listing_id, max(new_price)
from price_changes
where old_price < new_price and change_date >= '2016-01-01' and
      change_date < '2017-01-01'
group by listing_id;

You can then use this as a subquery to get the averages. The result is something like this:

select sum(new_price) / sum(built_area + used_area)
from (select listing_id, max(new_price) as new_price
      from price_changes
      where old_price < new_price and change_date >= '2016-01-01' and
            change_date < '2017-01-01'
      group by listing_id
     ) l join
     area a
     using (listing_id)
where built_area > 200 or used_area > 200;

Upvotes: 1

Related Questions