Group By子句中的列名错误无效

I'm trying to create a Sp for rdlc report. In which I'm using many user defined function for calculation. But when I'm trying to take function alias in group by clause it is givng an error "Msg 207, Level 16, State 1, Line 14 Invalid column name 'CommPaid'" Below is my Query

我正在尝试为rdlc报告创建一个Sp。其中我使用了许多用户定义的函数进行计算。但是,当我试图在group by子句中使用函数别名时,它会出现错误“消息207,级别16,状态1,行14无效列名'CommPaid'”以下是我的查询

SELECT        Employee.FirstName AS Tech, CommissionStructure.Name AS Commission, '$ ' + CONVERT(varchar(8), SUM(WorkOrderPayment.PaymentTotal)) 
                     AS TotalSales --, ISNULL(SUM(PurchaseOrder.AmountPaidToSupplier), 0) AS POAmount, ISNULL(SUM(WorkOrderPayment.TOC),0) AS TOC,
                    -- SUM(dbo.GetCashInvoiceAmount(WorkOrderPayment.WorkOrderPaymentID)) AS CashInvoice, ISNULL(SUM(dbo.GetSecTechAmount(WorkOrderPayment.WorkOrderID)),0) AS SecTechAmount
                    ,dbo.GetCommission(SUM(WorkOrderPayment.PaymentTotal),SUM(dbo.GetCashInvoiceAmount(WorkOrderPayment.WorkOrderPaymentID)),ISNULL(SUM(PurchaseOrder.AmountPaidToSupplier), 0),ISNULL(SUM(WorkOrderPayment.TOC),0),ISNULL(SUM(dbo.GetSecTechAmount(WorkOrderPayment.WorkOrderID)),0),CommissionStructure.CommissionPercentage) As CommPaid

FROM            WorkOrder INNER JOIN
                     WorkOrderPayment ON WorkOrder.WorkOrderID = WorkOrderPayment.WorkOrderID INNER JOIN
                     Employee ON WorkOrder.empID = Employee.empID INNER JOIN
                     CommissionStructure ON Employee.CommissionStructureID = CommissionStructure.CommissionStructureID INNER JOIN
                     [User] ON Employee.UserID = [User].UserID LEFT OUTER JOIN
                     PurchaseOrder ON WorkOrderPayment.WorkOrderPaymentID = PurchaseOrder.WorkOrderPaymentID

WHERE        (WorkOrder.OrderStatusID = 3) AND (WorkOrder.Deleted = 0) AND ([User].Active = 1) AND (CONVERT(varchar, WorkOrder.ScheduleStartTime, 101) 
                     >= '10/01/2013') AND (CONVERT(varchar, WorkOrder.ScheduleStartTime, 101) <= '10/24/2013')

GROUP BY Employee.FirstName, CommissionStructure.Name,CommPaid

When I removed function alias then it give an other error which is obvious.

当我删除函数别名时,它会给出一个明显的其他错误。

"Msg 8120, Level 16, State 1, Line 4
Column 'CommissionStructure.CommissionPercentage' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

2 个解决方案

#1

Place the complex function you are using in the SELECT also into the GROUP BY:

将您在SELECT中使用的复杂函数也放入GROUP BY中:

GROUP BY 
... 
,dbo.GetCommission(...

The point is, that your alias is not available during the GROUP BY processing. SELECT is usually the latest part of the statement to be executed...

关键是,您的别名在GROUP BY处理期间不可用。 SELECT通常是要执行的语句的最新部分......

#2

Remove CommPaid in GROUP BY

删除GROUP BY中的CommPaid

GROUP BY Employee.FirstName, CommissionStructure.Name

because by itself it is also an Aggregate field

因为它本身也是一个聚合字段

But if you want the two aggregate fields, that is, TotalSales, CommPaid - you have two options:

但是如果你想要两个聚合字段,即TotalSales,CommPaid - 你有两个选择:

Option 1: Include the complex function in your GROUP BY

选项1:在GROUP BY中包含复杂函数

GROUP BY Employee.FirstName, CommissionStructure.Name, dbo.GetCommission(SUM(WorkOrderPayment.PaymentTotal),SUM(dbo.GetCashInvoiceAmount(WorkOrderPayment.WorkOrderPaymentID)),ISNULL(SUM(PurchaseOrder.AmountPaidToSupplier), 0),ISNULL(SUM(WorkOrderPayment.TOC),0),ISNULL(SUM(dbo.GetSecTechAmount(WorkOrderPayment.WorkOrderID)),0),CommissionStructure.CommissionPercentage)

Option 2: Make two Query and JOIN the two, like:

选项2:进行两次查询并加入两者,如:

SELECT A.Tech, A.Commission, A.TotalSales, B.CommPaid FROM
(
SELECT   Employee.EmpID,  Employee.FirstName AS Tech, CommissionStructure.Name AS Commission, '$ ' + CONVERT(varchar(8), SUM(WorkOrderPayment.PaymentTotal)) 
                 AS TotalSales 

FROM            WorkOrder INNER JOIN
                 WorkOrderPayment ON WorkOrder.WorkOrderID = WorkOrderPayment.WorkOrderID INNER JOIN
                 Employee ON WorkOrder.empID = Employee.empID INNER JOIN
                 CommissionStructure ON Employee.CommissionStructureID = CommissionStructure.CommissionStructureID INNER JOIN
                 [User] ON Employee.UserID = [User].UserID LEFT OUTER JOIN
                 PurchaseOrder ON WorkOrderPayment.WorkOrderPaymentID = PurchaseOrder.WorkOrderPaymentID

WHERE        (WorkOrder.OrderStatusID = 3) AND (WorkOrder.Deleted = 0) AND ([User].Active = 1) AND (CONVERT(varchar, WorkOrder.ScheduleStartTime, 101) 
                 >= '10/01/2013') AND (CONVERT(varchar, WorkOrder.ScheduleStartTime, 101) <= '10/24/2013')

GROUP BY Employee.FirstName, CommissionStructure.Name
) A

INNER JOIN
SELECT A.Tech, A.Commission, A.TotalSales, B.CommPaid
(
SELECT   Employee.EmpID,  Employee.FirstName AS Tech, dbo.GetCommission(SUM(WorkOrderPayment.PaymentTotal),SUM(dbo.GetCashInvoiceAmount(WorkOrderPayment.WorkOrderPaymentID)),ISNULL(SUM(PurchaseOrder.AmountPaidToSupplier), 0),ISNULL(SUM(WorkOrderPayment.TOC),0),ISNULL(SUM(dbo.GetSecTechAmount(WorkOrderPayment.WorkOrderID)),0),CommissionStructure.CommissionPercentage) As CommPaid


FROM            WorkOrder INNER JOIN
                 WorkOrderPayment ON WorkOrder.WorkOrderID = WorkOrderPayment.WorkOrderID INNER JOIN
                 Employee ON WorkOrder.empID = Employee.empID INNER JOIN
                 CommissionStructure ON Employee.CommissionStructureID = CommissionStructure.CommissionStructureID INNER JOIN
                 [User] ON Employee.UserID = [User].UserID LEFT OUTER JOIN
                 PurchaseOrder ON WorkOrderPayment.WorkOrderPaymentID = PurchaseOrder.WorkOrderPaymentID

WHERE        (WorkOrder.OrderStatusID = 3) AND (WorkOrder.Deleted = 0) AND ([User].Active = 1) AND (CONVERT(varchar, WorkOrder.ScheduleStartTime, 101) 
                 >= '10/01/2013') AND (CONVERT(varchar, WorkOrder.ScheduleStartTime, 101) <= '10/24/2013')

GROUP BY Employee.FirstName, CommissionStructure.Name
) B
ON A.EmpID = B.EmpID

#1