multiple aggregation and nested queries

[dkalina]

I am trying to get a weighted average rating for photos based on the user group that rated them. I would like to do this forumla in SQL (not pull it back out into a programming language because I would like to pull the results in groups of 10 to display in an app.

The query I currently have returns all the data in row form but doesn't allow me to work with the aggregates, I need to do aggregates like averaging g1 and g2 as well as a couple if statements . I am getting the feeling that I am going to have to redo my nested queries to put this stuff in the FROM area. I tried some but it does not work. Here is the query I am using:

select Distinct users.id, users.first_name, users.last_name, users.dir_name, photos.id, photos.photo_name, photos.

photo_desc, photos.date_created, photos.hilite, count(photos.id) as counter,
(
select IFNULL(SUM(a.my_vote)/count(a.id),0) from votes as a where a.user_type = 3 and a.artist = users.id
) as g1,
(
select IFNULL(SUM(a.my_vote)/count(a.id),0) from votes as a where a.user_type = 2 and a.artist = users.id
) as g2,
(
select IFNULL(SUM(a.my_vote)/count(a.id),0) from votes as a where a.user_type = 5 and a.artist = users.id
) as g3,
(
select IFNULL(SUM(a.my_vote)/count(a.id),0) from votes as a where a.user_type = 4 and a.artist = users.id
) as g4

From users, photos
left join photos ctr On photos.artist=users.id
WHERE users.id=photos.artist AND ctr.artist=users.id AND users.is_artist = 1
GROUP BY users.id, ctr.artist HAVING counter > 5
ORDER BY photos.hilite
;

Any help is most appreciated.