CODEWITHSUNDEEP
mysqlselectaverage
Laxmidi
Laxmidi

Reputation: 2684

MySQL Average: Making Statement More Efficient

I'm using this MySQL statement to find a neighborhood's average property assessment. The problem is that I have to add a SELECT statement for each neighborhood-- and there are a lot of neighborhoods. Is there a way to do it without having to specify 'RIVER FRONT' or 'OLD TOWN'? Is there a more efficient way?

SELECT AVG(property_table.assessment) 
FROM property_table, neighborhood_table  
WHERE property_table.id = neighborhood_table.id AND neighborhood_table.neighborhood = 'RIVER FRONT'
    UNION ALL
SELECT AVG(property_table.assessment) 
FROM property_table, neighborhood_table  
WHERE property_table.id = neighborhood_table.id AND neighborhood_table.neighborhood = 'OLD TOWN'

Upvotes: 0

Views: 94

Answers (2)

Marc B
Marc B

Reputation: 360792

SELECT AVG(property_table.assessment), neighborhood_table.neighborhood
FROM property_table
INNER JOIN neighborhood_table ON property_table.id = neighborhood_table.id
WHERE neighborhood_table.neighborhood IN ('RIVER_FRONT', 'OLD TOWN')
GROUP BY neighborhood_table.neighborhood;

Upvotes: 0

gbn
gbn

Reputation: 432541

This is an standard SQL aggregate statement and shows averages for all neighbourhoods
The left join allows you to get zero values where no property exists in a neighbourhood.

SELECT 
    n.neighborhood, COALESCE(AVG(p.assessment), 0)
FROM
    neighborhood_table n
    LEFT JOIN 
    property_table p ON p.id = n.id
GROUP BY 
    n.neighborhood;

Upvotes: 1

Related Questions