Re: What's the correct select

"Jaff Fan" <jaff_at_tang.ca> wrote in message news:HpBD9.149581$oRV.82069_at_news04.bloor.is.net.cable.rogers.com...
> Hi all,
>
> Here is a table marks:
>
> std_id number(4)
> std_name varchar2(30)
> subj1 number(3)
> subj2 number(3)
>
> select statment:
>
> select std_id, subj1+subj2 Total_Marks from marks
> where subj1 > avg(subj1) and subj2 > avg(subj2)
> order by std_id
>
> The statment returns an error at where clause, so what's the correct where
> clause
>
> Thanks
>
> Jaff

You cannot use group functions in a where clause, therefore, you cannot use functions such as avg() in the where clause, unless you use an inline select statement.

As a thought try this. I tested a similar SQL statement (of course I did not have your data or tables) and it worked.

Dennis

select std_id, subj1+subj2 Total_Marks
from marks
where subj1 >
(

    select avg(subj1)
    from marks
)
and subj2 >
(

    select avg(subj21)
    from marks
)
order by std_id Received on Sat Nov 23 2002 - 08:01:44 CET