I have a cities and a votes database.
CITIES contains id,name,province fields.
VOTES contains id,city,water,sanitation,elec,roads,health,housing fields.
I am using the following statement to get initial data...
SELECT cities.name,cities.province, ROUND(AVG(votes.water)) as water, ROUND(AVG(votes.elec)) as elec, ROUND(AVG(votes.sanitation)) as sanitation, ROUND(AVG(votes.housing)) as housing, ROUND(AVG(votes.roads)) as roads, ROUND(AVG(votes.health)) as health, COUNT(votes.id) AS votes FROM cities INNER JOIN votes ON votes.city = cities.id GROUP BY cities.id ORDER BY votes DESC
This produces the following results...
What I now need to add to the above statement is to create a field that averages water,elec,sanitation,housing,roads and health from the generated results.
Is that possible?