Filter results by number of rows in mysql

Question

I have three tables:

houses

id | square_feet
------------------
1  |   2000
2  |   1600
3  |   1000

energies

id | house_id | kwh
-------------------------
6  |   1      |  10  
7  |   2      |  100
8  |   3      |  200

cars

id  | energy_id | gallons
--------------------------
11  |    6      |   20
12  |    6      |   40
13  |    7      |  200
14  |    8      |   77
15  |    8      |   88

I need to get the average square feet and average kwh for either all houses or just the houses with a certain number of cars.

So for two cars, I want values for houses 1 and 3. For one car, I want values only for house 2. For all houses, I want 1, 2 and 3.

For any all houses I am using:

SELECT AVG(houses.square_feet) AS avg_sf, AVG(energies.kwh) AS avg_kwh 
  FROM (houses LEFT JOIN energies ON houses.id = energies.house_id)

How do I find the average square feet and average kwh for houses that have two cars?

Answer 1

Use:

SELECT AVG(h.square_feet), 
       AVG(e.kwh)
  FROM ENERGIES e
  JOIN HOUSES h ON h.id = e.house_id
  JOIN (SELECT c.energy_id
          FROM CARS s
      GROUP BY e.energy_id
        HAVING COUNT(*) = 2) x ON x.energy_id = e.id

Because of the foreign key relationship, you can use a JOIN rather than LEFT JOIN between the ENERGIES and HOUSES table.

Update to add that you can change:

HAVING COUNT(*) = 2

...to whatever number you like depending on the number of cars you want to know about.

Answer 2

I'd use a WHERE ... IN construct. A JOIN risks changing the averages by duplicating energy rows.

SELECT 
    AVG(houses.square_feet) AS avg_sf, 
    AVG(energies.kwh) AS avg_kwh 
FROM houses 
LEFT JOIN energies ON houses.id = energies.house_id
WHERE houses.house_id in (
    SELECT energies.house_id
    FROM energies 
    JOIN cars ON cars.energy_id = energies.id
    GROUP BY energies.house_id
    HAVING count(distinct cars.id) = 2
)
GROUP BY houses.id

This even works if one house has multiple links to a single car through the energies table, the COUNT DISTINCT should make sure each car is only counted once.