sql - Error when using join and join produces error

Question

I am trying to make groups and make joins with the below tables but I get an

ORA-00918: column ambiguously defined

error.

Any ideas how to fix?

SELECT staffn, job, COUNT(*)"staffcount",  AVG(sal)"AverageSal"
FROM staff, shop
 WHERE  staff.shopno= shop.shopno
 GROUP BY shopno, job;

question from:https://stackoverflow.com/questions/65643184/error-when-using-join-and-join-produces-error

Answer 1

You should use proper alias and your group by clause must include all the unaggregated columns as follows:

SELECT s.staffn, sh.job, 
       COUNT(*)"staffcount",  
       AVG(s.sal)"AverageSal"
  FROM staff s join shop sh
 WHERE s.shopno= sh.shopn
 Group by s.staffn, sh.job