0

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?

Programmer
  • 37
  • 6

1 Answers1

1

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

http://sqlfiddle.com/#!9/93027a/2

Utsav
  • 7,914
  • 2
  • 17
  • 38
  • If it worked, then please accept the answer by clicking on `tick` symbol on left of the answer so it could be closed. Thanks. – Utsav Jan 24 '16 at 08:48
  • Sorry, I do not have enough reputation, therefore while clicking on tick , it shows warning message. But, as soon as I have, I will mark it as best. Thanks again! – Programmer Jan 24 '16 at 08:54
  • @Programmer, if you asked the question, you should be able to select the correct answer, regardless of your reputation. You may not be able to upvote, but you can designate this answer as correct. – Hambone Jan 25 '16 at 03:14