Re: What's the correct select

"Dennis" <dlvatwork_at__hotmail_.com> wrote in message news:sJFD9.9094$ea.297739_at_news2.calgary.shaw.ca...
> "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
>
>
I had an error in my sql syntax and have corrected it in the version below, hope I caught the only mistake ;)
Sorry
Dennis

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

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

     select avg(subj2)
     from marks