At 07:41 AM 9/02/2006, you wrote:

>Hello.
>
>This is a prettily tricky query in fact.
>Here it is:
>
>select Messages.*,avg(MessageRatings.ratingvalue) as AvgRate
>from Messages
>join messageratings mg on mg.message_id = Messages.message_id
>group by AvgRate
>
>WHAT I WANT TO DO
>-----------------
>
>I simply want to group messages by the ratings they received from
>users, that is - the ones higher rated get showed up before less
>rated ones. All, of course, relative to thread.
>
>This is my database structure:
>
>CREATE TABLE MESSAGES (
> MESSAGE_ID INTEGER NOT NULL,
> PARENTMESSAGE_ID INTEGER,
> USER_ID INTEGER NOT NULL,
> SUBJECT VARCHAR(255) NOT NULL,
> MESSAGEBODY BLOB SUB_TYPE 1 SEGMENT SIZE 1000 NOT NULL
>);
>
>CREATE TABLE MESSAGERATINGS (
> RATINGID INTEGER NOT NULL,
> MESSAGE_ID INTEGER NOT NULL,
> RATINGVALUE SMALLINT NOT NULL
>);
>
>Let's make an example:
>
>Say that I have the tables this way:
>
>MESSAGE_ID PARENTMESSAGE_ID SUBJECT MESSAGEBODY
>1 0 Very Valuable message Indeed very valuable
>2 1 Not very valuable This one's ok
> response, still ok tho
>3 1 Awful response This one's not worth
>4 0 Awful message This one's not worth
>5 4 Very valuable response Indeed very valuable
>6 4 Awful response This one's not worth
>7 4 Not very valuable This one's ok
> response, still ok tho
>Now we have the ratings table:
>
>RATING_ID MESSAGE_ID RATINGVALUE
>1 1 09
>2 2 07
>3 3 02
>4 4 01
>5 5 09
>6 6 01
>7 7 06
>
>Now, I want to see this result set(well, I actually want more fields,
>this is just to give you an idea)
>
>MESSAGE_ID SUBJECT AvgRating Parent_ID
>1 Very valuable message 9 0
>4 Awful message 1 0

The immediate reason for the error message you're getting is that you
have fields in the query's column specs that are not included in the
aggregation. In fact, the aggregation occurs over columns in
MessageRatings, not Messages. The only thing you want from Messages
is the Subject and Parent_ID that correspond to the Message_ID group
in Messages. Otherwise, you are not interested in Messages at all,
which makes selecting Messages.* (which includes columns not
involved in the aggregation) the underlying cause of your problem.

But you have other problems here, too:

group by AvgRate

1. You don't get your grouping by using the aggregating expression
in the Group By clause. You use the *columns* that define the
layering of the grouping.

2. You can't (in 1.5.3 and lower) refer to a derived column by its
name. You must either re-state the expression or take the shortcut
of referring to the expression by its "degree" (the ordinal number of
the specified column, counting from 1 at the left).

To get the output you describe (average ratings for messages within
the each Parent_ID group), make your query like this:

select
mr.Message_ID,
m.Subject,
avg(mr.rating_value) as AvgRating,
m.Parent_ID
from Messages m
join MessageRatings mr
on m.Message_ID = mr.Message_ID
group by 4,1, 2

>Now, if I want to see the responses to Message_ID 1 then running the
>query I'll see this result:
>
>MESSAGE_ID SUBJECT AvgRating Parent_ID
>2 Not very valuable 7 1
> response, still ok tho
>3 Awful response 1 1
>
>If I then query for the Message_ID 4 I'll see this:
>
>MESSAGE_ID SUBJECT AvgRating Parent_ID
>5 Very valuable response 9 4
>7 Not very valuable 7 4
> response, still ok tho
>6 Awful response 1 4
>
>Do I make sense? :D

Not really! :-) Do you mean "...for Parent_ID 1 [or 4, in the second
case]" ?

Then, just make a parameterised WHERE clause restricting the output
to just the Parent_ID that you want:

select
mr.Message_ID,
m.Subject,
avg(mr.rating_value) as AvgRating,
m.Parent_ID
from Messages m
join MessageRatings mr
on m.Message_ID = mr.Message_ID
where m.Parent_ID = ?
group by 4,1, 2

./heLen