I have a problem in the following sql code.I have a table like this: Student(st_id,st_fname,st_lname) and Visitings(visit_id,st_id,visit_cost).
What I want to do is to select average visit_cost for per student where it is more than 500, then st_fname and st_lname. And I have written the following code:
select avg (v.visit_cost) as average_cost, s.st_fname, s.st_lname
from student s
inner join visitings v on s.st_id=v.st_id
group by st_id
having avg(v.visit_cost)>=500;
In output it shows "column ambiguously defined" error. If possible could you help me with this please?
As you did't answer the questions in comments, I am assuming that amount_paid column is coming from visitings table as student table looks like a dimension table.
The column ambiguity error is due to st_id column which is there in both tables. So SQL doesn't know which table's st_id are you referring to. Try this.
select avg (v.visit_cost) as average_cost, s.st_fname, s.st_lname
from student s
inner join visitings v on s.st_id=v.st_id
group by s.st_id
having avg(v.amount_paid)>=500;
See SQLFiddle demo here