April 16, 2015 at 1:10 pm
I have to write a query using a subquery within the from clause to get the top 5 customers based on number of orders. The outer query should display the customer id,date of first order,date of last order, and total orders received. The problem that i am having is that in my subquery where i have dbo.Customers.orderid Im getting an error saying the multi part identifier can not be bound. What am i missing in my query? Can someone help thanks
here is the query i tried
SELECT CustomerID, MAX(OrderDate) AS LastOrder, MIN(OrderDate) AS FirstOrder, COUNT(OrderID) AS [TotalOrders]
FROM dbo.Orders join
( SELECT Top 5 CustomerID As TopCustomer
FROM dbo.customers
group by customerid
order by TopCustomer desc) As TopCustomer
ON dbo.Customers.orderid = dbo.Orders.CustomerID
April 16, 2015 at 1:21 pm
Looks like you have duplicate column names in both tables but you are not using a table alias
You query should look something like this:
SELECT a.col1, a.col2, b.col1
FROM a
Join
(
<Subquery>
) AS b
ON a.col1 = b.col2
Can you post the DDL for both tables?
-- Itzik Ben-Gan 2001
April 16, 2015 at 1:31 pm
Alan.B (4/16/2015)
Looks like you have duplicate column names in both tables but you are not using a table aliasYou query should look something like this:
SELECT a.col1, a.col2, b.col1FROM a
Join
(
<Subquery>
) AS b
ON a.col1 = b.col2
Can you post the DDL for both tables?
Further on Alan's request, how about pitching in some usable sample data in the form of an insert statement?
😎
BTW, this is my guess of how the query should look like, mind you that once an alias is defined for an object/table, it goes by that alias, no schema qualification is applicable after that point (just like marriage:-P)
SELECT
ORD.CustomerID
,MAX(ORD.OrderDate) AS LastOrder
,MIN(ORD.OrderDate) AS FirstOrder
,COUNT(ORD.OrderID) AS [TotalOrders]
FROM dbo.Orders ORD
INNER JOIN
(
SELECT
Top 5 CustomerID As TopCustomer
FROM dbo.customers
group by customerid
order by TopCustomer desc
) As TopCustomer
ON ORD.CustomerID = TopCustomer.TopCustomer;
April 20, 2015 at 8:29 am
I will also try this
April 20, 2015 at 8:31 am
Eirikur Eiriksson when i try the query that way i get an error saying dbo.orders.customerid is invalid in the select list because
it is not contained in either an aggregate function or the groupby clause
April 20, 2015 at 8:36 am
karodhill (4/20/2015)
Eirikur Eiriksson when i try the query that way i get an error saying dbo.orders.customerid is invalid in the select list becauseit is not contained in either an aggregate function or the groupby clause
Quick guess as you haven't provided the information requested earlier
😎
Remove the semicolon and append this to the query
GROUP BYORD.CustomerID;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply