Reputation: 159

Error with SQL code

Trying to execute this code with COALESCE to group data together. When executing I see this error

Msg 207, Level 16, State 1, Line 11 Invalid column name 'OrderID'. Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'CustomerID'.

SELECT CustomerID,
FirstName,
LastName,
COALESCE(OrderIDCnt,0),
COALESCE(SKUCnt,0),
COALESCE(OrderTotal,0)
FROM Customer as c
left join (SELECT o.CustomerID,
       SUM(OrderTotal) AS OrderTotal,
       COUNT(OrderedProductSKU) AS SKUCnt,
       COUNT(OrderID) AS OrderIDCnt
       FROM Orders as o
       inner join Orders_ShoppingCart as osc
       on osc.OrderNumber=o.OrderNumber
       and osc.CustomerID=o.CustomerID
       GROUP BY o.CustomerID
      )ord
ON ord.CustomerID = c.CustomerID

Upvotes: 2

Answers (3)

Sergey Kalinichenko

Reputation: 726579

The error means that SQL parser cannot uniquely resolve the unqialified name CustomerID. There are two candidates there:

The CustomerID field of the Customer table, and
The CustomerID field of the ord sub-query.

Although the two must match, because your join condition requires that ord.CustomerID = c.CustomerID, the query parser cannot arbitrarily pick one for you. Therefore, you need to either disambiguate this manually by specifying c or ord in front of CustomerID, or pick a different name for CustomerID in the subquery.

The first approach:

SELECT c.CustomerID, -- Add c. in front of CustomerID
FirstName,
LastName,
COALESCE(OrderIDCnt,0),
COALESCE(SKUCnt,0),
COALESCE(OrderTotal,0)
FROM Customer as c
left join (SELECT o.CustomerID,
       SUM(OrderTotal) AS OrderTotal,
       COUNT(OrderedProductSKU) AS SKUCnt,
       COUNT(o.OrderID) AS OrderIDCnt -- Disambiguated OrderID
       FROM Orders as o
       inner join Orders_ShoppingCart as osc
       on osc.OrderNumber=o.OrderNumber
       and osc.CustomerID=o.CustomerID
       GROUP BY o.CustomerID
      )ord
ON ord.CustomerID = c.CustomerID

The second approach:

SELECT CustomerID,
FirstName,
LastName,
COALESCE(OrderIDCnt,0),
COALESCE(SKUCnt,0),
COALESCE(OrderTotal,0)
FROM Customer as c
left join (SELECT o.CustomerID as OrderCustomerID, -- add an alias
       SUM(OrderTotal) AS OrderTotal,
       COUNT(OrderedProductSKU) AS SKUCnt,
       COUNT(o.OrderID) AS OrderIDCnt -- Disambiguated OrderID
       FROM Orders as o
       inner join Orders_ShoppingCart as osc
       on osc.OrderNumber=o.OrderNumber
       and osc.CustomerID=o.CustomerID
       GROUP BY o.CustomerID
      )ord
ON ord.OrderCustomerID = c.CustomerID -- Rename the field

Edit : Disambiguated OrderID.

Upvotes: 2