May 31, 2015 at 12:22 pm
Hello,
I'm trying to write a query that returns the FullName (First Name plus Last Name), TotalOrders (count of all orders) and a calculated field called SalesGrouping for all employees. For the SalesGrouping field, the value should be either 1, 2 or 3. Employees who are in the top third of sales as defined by total number of orders would get a 1, and so on. The code I have so far is below. I'm receiving error messages saying that I have Msg 207, Level 16, State 1, Line 6, invalid column names for FullName, TotalOrders and SalesGrouping. Can anyone help me with what I'm doing wrong here? Thank you very much in advance for your help. --RM
SELECT firstname + N' ' + lastname AS FullName, COUNT(orderid) AS TotalOrders,
NTILE(3) OVER(ORDER BY orderid) AS SalesGrouping
FROM dbo.Employees AS Employees
INNER JOIN Sales.Orders AS Orders
ON Employees.empid = Orders.empid
GROUP BY FullName, TotalOrders, SalesGrouping;
June 1, 2015 at 4:27 am
You can't use the column aliases in the GROUP BY clause; you would either need to repeat the definitions or wrap them in a CTE as below;
;WITH OrderGroups as
(SELECT [orderid]
,FirstName + N' ' + LastName as FullName
,NTILE(3) OVER (ORDER BY orderid) as SalesGrouping
FROM dbo.Employees as Employees
INNER JOIN Sales.Orders as Orders on Employees.empid = Orders.empid)
SELECT FullName, count(orderid) as TotalOrders, SalesGrouping
FROM OrderGroups
GROUP BY FullName, SalesGrouping
June 1, 2015 at 6:38 am
;
WITH SalesPerEmp AS (
SELECT
e.firstname + N' ' + e.lastname AS FullName,
COUNT(*) AS TotalOrders
FROM dbo.Employees e
INNER JOIN Sales.Orders o
ON e.empid = o.empid
GROUP BY e.firstname + N' ' + e.lastname
)
SELECT
FullName,
TotalOrders,
NTILE(3) OVER(ORDER BY TotalOrders DESC) AS SalesGrouping
FROM SalesPerEmp
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 1, 2015 at 7:08 am
Yes, use ChrisM's version. Mine would give SalesGrouping according to orderid rather than number of orders.
June 1, 2015 at 11:42 am
Thank you for your help!
RM
June 1, 2015 at 11:46 am
Hi Chris M,
On your response below, why does the code start with a semicolon? Is SalesPerEmp functioning as a stored procedure? Thanks, RM
;
WITH SalesPerEmp AS (
SELECT
e.firstname + N' ' + e.lastname AS FullName,
COUNT(*) AS TotalOrders
FROM dbo.Employees e
INNER JOIN Sales.Orders o
ON e.empid = o.empid
GROUP BY e.firstname + N' ' + e.lastname
)
SELECT
FullName,
TotalOrders,
NTILE(3) OVER(ORDER BY TotalOrders DESC) AS SalesGrouping
FROM SalesPerEmp
June 1, 2015 at 11:55 am
CTEs require that the statement before the CTE is terminated with a semi-colon, so one is generally added in front of the definition of the CTE. Kinda weird, but there you go.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply