Ambiguous column when make a normal inner join

Question

Hi everyone, any suggestion about the error below?

select sum(salesamount),
 CalendarYear, sum(orderquantity), p.EnglishProductName from FactInternetSales i inner join DimProduct p on p.ProductKey=i.ProductKey 
 inner join   dimdate d on d.DateKey=i.DueDateKey inner join DimCustomer o 
 on i.CustomerKey=o.CustomerKey inner join DimGeography g on o.GeographyKey=g.GeographyKey where g.EnglishCountryRegionName='australia' group by CalendarYear, ProductKey order by ProductKey, CalendarYear asc

Ambiguous column name 'productkey'.

Without the join with product everything works...

Answer 1

On the group by and order by clause you need to give the correct table alias for  CalendarYear, ProductKey

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

try this

select sum(salesamount), CalendarYear, sum(orderquantity), p.EnglishProductName 
 from FactInternetSales i inner join DimProduct p on p.ProductKey=i.ProductKey 
 inner join   dimdate d on d.DateKey=i.DueDateKey
 inner join DimCustomer o  on i.CustomerKey=o.CustomerKey 
 inner join DimGeography g on o.GeographyKey=g.GeographyKey 
 where g.EnglishCountryRegionName='australia' 
 group by CalendarYear, p.ProductKey order by p.ProductKey, CalendarYear asc

If u are using the columnname common for two tables, then its always mandatory to specify alias name when used in group By or Oreder By clause.

---

Please have look on the comment