Averaging out Rows

Databases
1

Hi All

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?

2

you can try the WITH ROLLUP option on the GROUP BY clause, although you will ahve to ditch your ORDER BY clause