Error with SQL code

Question

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

Please tag the question according to the SQL you are using (MySql, Sql-Server, Oracle, etc.) — Fabian Bigler
– Fabian Bigler, Commented Jan 23, 2014 at 16:27
SELECT c.CustomerID, c.FirstName, c.LastName, COALESCE(c.OrderIDCnt,0), COALESCE(c.SKUCnt,0), COALESCE(c.OrderTotal,0) FROM Customer as c left join (SELECT o.CustomerID, SUM(o.OrderTotal) AS OrderTotal, COUNT(o.OrderedProductSKU) AS SKUCnt, COUNT(o.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 — Gayathri
– Gayathri, Commented Jan 23, 2014 at 16:30

marcosh · Accepted Answer · 2014-01-23 16:27:54Z

3

try to use c.CustomerId or ord.CustomerId

answered Jan 23, 2014 at 16:27

marcosh

9,0386 gold badges49 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sergey Kalinichenko · Accepted Answer · 2014-01-23 16:45:45Z

2

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.

edited Jan 23, 2014 at 16:45

answered Jan 23, 2014 at 16:31

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

2 Comments

Dean Over a year ago

Thanks.Renaming that field resolves one issue. The other one still remains Msg 207, Level 16, State 1, Line 11 Invalid column name 'OrderID'.

Sergey Kalinichenko Over a year ago

@DeanFraser-Phillips I see - there's also OrderID on the Orders_ShoppingCart table, right? I added a disambiguation for that, too - please see the edit.

gasparms · Accepted Answer · 2014-01-23 16:32:12Z

0

You have field named customerId in tables Orders and Customer and you don't specify which you want to include in select clause.

In this case you always should type the field in format alias.field

answered Jan 23, 2014 at 16:32

gasparms

3,35424 silver badges26 bronze badges

Collectives™ on Stack Overflow

Error with SQL code

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related