query with less than and greater than statements in where clause

Question

SELECT ( Sum(r.marks_attained) / Sum(r.total_marks) * 100 )AS marks,
       s.name
FROM   result r,
       student s
WHERE  r.student_id = s.student_id
GROUP  BY r.student_id
AND 
ORDER  BY marks DESC

How i can get only students marks with percentage greater than 90?

can i use marks in where clause?

This previous answer should help you. You can use `HAVING` or repeat the expression in the where clause http://stackoverflow.com/a/200203/945775 — AgmLauncher, Jul 23 '13 at 08:47

score 2 · Answer 1 · answered Jul 23 '13 at 08:47

Try this:

select (sum(r.marks_attained)/sum(r.total_marks)*100)as marks,s.name
from result r ,student s
where r.student_id=s.student_id 
group by r.student_id 
having (sum(r.marks_attained)/sum(r.total_marks)*100) >= 90
order by marks desc

I remove by your query an AND between where and group by, so I added a having clause

P.S: >= 90 (if you want to include 90, > 90 otherwise)

score 0 · Answer 2 · edited Jul 23 '13 at 09:23

No, You can not use marks in where clause. because its a aggregate function. It can only be used in having clause.

SELECT (sum(r.marks_attained)/sum(r.total_marks)*100) as marks,
    s.name from result r, student s
WHERE
    r.student_id=s.student_id
GROUP BY r.student_id
HAVING
    (sum(r.marks_attained)/sum(r.total_marks)*100) > 90
ORDER BY marks DESC

or

SELECT (sum(r.marks_attained)/sum(r.total_marks)*100) as marks,
   s.name from result r,
   student s
WHERE
   r.student_id=s.student_id 
GROUP BY r.student_id
HAVING marks > 90
ORDER BY marks DESC

query with less than and greater than statements in where clause

2 Answers2