2 table query problem

[glossen]

Im having trouble with this query. I have 2 tables. I only want to display
the events table but I want to sort the results based on the users average
rating on the article(events).

SELECT events.evSubject, events.evID, AVG(ev_rating.rating) AS "rating" FROM
events, ev_rating WHERE events.evType = 12 AND events.evPrivate = 0 AND
events.evStat = 3 AND ev_rating.rtID = events.evID ORDER BY rating

I get error: General error: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause

Im not a huge sql guru but I know most of you are. I wasnt sure how else to
do this or sort the list like I want.

[paross1]

Like the error message said, since you are performing an aggregate function
without a GROUP BY.

SELECT events.evSubject, events.evID, AVG(ev_rating.rating) AS "rating"
FROM events, ev_rating
WHERE events.evType = 12
AND events.evPrivate = 0
AND events.evStat = 3
AND ev_rating.rtID = events.evID
GROUP BY events.evSubject, events.evID
ORDER BY rating

Phil

[glossen]

Thanks Phil. Im having an issue with that query. It wasnt sorting by the
average rating for that record. That table(events) only has 5 records right
now. But each event has many ratings (hence the AVR). I put that query in and
the results were as follows:

evID Subject rating
1 event1 3.758
2 event2 3.758
3 event3 3.758

and so on.... I know what they should be but I se that it just took an average
once, do ya kinda see what im saying?? Is there any way to retrieve the unique
average for each event?

Thank You