Filter results by number of rows in mysql
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_开发者_Go百科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?
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.
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.
精彩评论