如何解决SQL报错：‘查询未将[Invoice Paid Date]包含在聚合函数中’

我是SQL新手，最近在从外部工作簿做分组查询时遇到报错，错误提示：
Your query does not include the specified expression '[Invoice Paid Date]' as part of an aggregate function.

我用的SQL语句是：

SELECT DISTINCTROW [Worker],[Time Sheet End Date], Sum([Time Sheet Billable Hours]),First([Invoice ID]),First([Invoice Status]), Sum([Invoice Paid Amount]), Sum([Total Invoice Amount]), First([Invoice Paid Date]), First([Initial Invoice submit date]) 
FROM [Timesheet_and_Invoice_Details$A2:Q5000] 
Where [Worker] Is Not Null  
GROUP BY [Worker], [Time Sheet Status], [Time Sheet End Date] 
Order by [Invoice Paid Date] Desc;

我试过用MAX函数处理[Invoice Paid Date]字段，但问题还是没解决。

输入数据说明 #

输入数据包含Worker、工时结束日期、可计费工时、发票ID、发票状态、已付金额、发票总金额、付款日期、首次提交日期等字段，同一Worker和工时结束日期有多条重复记录。

期望输出说明 #

按Worker和工时结束日期分组，汇总可计费工时、已付金额、发票总金额，同时保留对应发票的相关信息，最后按发票付款日期倒序排列。

问题原因与修正方案 #

报错的核心是ORDER BY里的[Invoice Paid Date]既不在GROUP BY里，也没被聚合函数包裹——哪怕SELECT里用了First/MAX，ORDER BY里也必须明确用聚合函数引用这个字段，不能直接写原字段名。另外原GROUP BY里的[Time Sheet Status]在SELECT中没出现，如果不需要按它分组可以去掉，避免拆分不必要的组。

修正后的SQL：

SELECT 
    [Worker],
    [Time Sheet End Date],
    Sum([Time Sheet Billable Hours]) AS [总可计费工时],
    First([Invoice ID]) AS [发票ID],
    First([Invoice Status]) AS [发票状态],
    Sum([Invoice Paid Amount]) AS [已付发票总额],
    Sum([Total Invoice Amount]) AS [发票总金额],
    Max([Invoice Paid Date]) AS [发票付款日期],
    First([Initial Invoice submit date]) AS [首次发票提交日期]
FROM [Timesheet_and_Invoice_Details$A2:Q5000]
WHERE [Worker] IS NOT NULL
GROUP BY [Worker], [Time Sheet End Date]
ORDER BY Max([Invoice Paid Date]) DESC;

如果确实需要按[Time Sheet Status]分组，记得把它加到SELECT列表里，比如加上First([Time Sheet Status]) AS [工时状态]，同时保留GROUP BY里的该字段。

内容的提问来源于stack exchange，提问作者Gubba Nub Nub