Sorting over a sum from two tables in mySQL

Question

I can't figure out a query that will add and compare across tables. I have three tables:

house

id
-----
 1
 2

month

id | house_id | btus
---------------------
 3 |   1      | 100
 4 |   2      | 200

car

id | month_id |  btu
--------------------------
 5 |   3      |  10
 6 |   4      |  20
 7 |   3      |  15

I need a query that will return the house ids sorted by total btus for the month and car.

So for the above example it would return 2,1 as (200+20) > (100 + 10 + 15)

Answer 1

SELECT  h.*
FROM    house
ORDER BY
        (
        SELECT  SUM(c.btu)
        FROM    month m
        JOIN    cars c
        ON      c.month_id = m.id
        WHERE   m.house_id = h.id
        ) +
        (
        SELECT  SUM(m.btus)
        FROM    month m
        WHERE   m.house_id = h.id
        )
        DESC

, or this one (probably slightly more efficient):

SELECT  h.*
FROM    house
ORDER BY
        (
        SELECT  SUM
                (
                btus +
                (
                SELECT  SUM(btu)
                FROM    cars c
                WHERE   c.month_id = m.id
                )
                )
        FROM    month m
        WHERE   m.house_id = h.id
        )
        DESC

Answer 2

Probably not the most performant use of joins, but:

  SELECT house.id 
    FROM house JOIN month JOIN car
   WHERE house.id = month.house_id AND month.id = car.month_id
GROUP BY house.id
ORDER BY sum(car.btu) + sum(month.btus);

The joins and where clause will explode the tables (try it with SELECT * and skipping the group/order clauses), group by will flatten them to one row each, and the sum()s will do the relevant math.