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...
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
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?
you can try the WITH ROLLUP option on the GROUP BY clause, although you will ahve to ditch your ORDER BY clause