时间戳

首页 > 解决方案 > 您的查询不包含指定表达式作为聚合函数的一部分，如何修复错误？

sql - 您的查询不包含指定表达式作为聚合函数的一部分，如何修复错误？

问题描述

如何更正此代码以修复我收到的“您的查询不包含指定表达式作为聚合函数的一部分”的错误

SELECT CUSTOMER.ACCOUNT_NUM, CUSTOMER.CUSTOMER_NAME, CUSTOMER.BR_NUM, CUSTOMER.REG_NAME, ORDER.ORDER_TYPE, ORDER.COMP_DT, ORDER.ISSUE_DT, [ORDER]![ACCOUNT_NUM] & [ORDER]![ORDER_TYPE] AS [Key], INTO Final Output
FROM (ORDER INNER JOIN CUSTOMER ON ORDER.ACCOUNT_NUM = CUSTOMER.ACCOUNT_NUM) 
WHERE (((CUSTOMER.INDQ)=0) AND ((CUSTOMER.PLAN_IND)=1))
GROUP BY CUSTOMER. ACCOUNT_NUM, CUSTOMER. CUSTOMER_NAME, CUSTOMER.BR_NUM, CUSTOMER.REG_NAME, ORDER.ORDER_TYPE, ORDER.COMP_DT, ORDER.ISSUE_DT, 
HAVING (((ORDER.COMP_DT)=20210129));

标签： sqlms-accessgroup-byms-access-2010aggregate-functions

解决方案

问题是GROUP BY. 我会推荐一些其他的改变：

  • 将HAVING子句移动到WHERE子句。
  • 使用表别名。

您还有一些无关的逗号。

所以：

SELECT c.ACCOUNT_NUM, c.CUSTOMER_NAME, c.BR_NUM, c.REG_NAME, 
       o.ORDER_TYPE, o.COMP_DT, o.ISSUE_DT,
       o.[ACCOUNT_NUM] & o.[ORDER_TYPE] AS [Key]
INTO Final Output
FROM (ORDER as o INNER JOIN
      CUSTOMER as c
      ON o.ACCOUNT_NUM = c.ACCOUNT_NUM) 
WHERE c.INDQ = 0 AND c.PLAN_IND = 1 AND
      o.COMP_DT = 20210129
GROUP BY c.ACCOUNT_NUM, c.CUSTOMER_NAME, c.BR_NUM, c.REG_NAME, 
         o.ORDER_TYPE, o.COMP_DT, o.ISSUE_DT, 
         o.[ACCOUNT_NUM] & o.[ORDER_TYPE]

推荐阅读