tSQL错误在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中

Question

这是我通过SAP查询工具创建的查询:

SELECT DISTINCT 
   T1.CardCode, 
   T1.CardName, 
   T1.Phone1, 
   T1.GroupCode, 
   T1.Territory,
   T2.Street, 
   T2.Block, 
   T2.City, 
   T2.ZipCode, 
   T2.County, 
   T2.State, 
   T2.Country,
   count(distinct(T3.DocEntry)) as NumberOfInvoices,
   sum(distinct(T4.GTotal)) as InvoiceTotal
FROM 
    [ASAP].[dbo].[OCRD] as T1
    INNER JOIN  [ASAP].[dbo].[CRD1] as T2 ON T1.CardCode = T2.CardCode
    INNER JOIN  [ASAP].[dbo].[OINV] as T3 on T1.CardCode = T3.CardCode
    INNER JOIN  [ASAP].[dbo].[INV1] as T4 on T3.DocEntry = T4.DocEntry
WHERE 
    T1.Territory = [%0] 
    AND T3.DocDate >= [%1] 
    AND T3.DocDate <= [%2] 
GROUP BY 
    T1.CardCode,
    T1.CardName, 
    T1.Phone1, 
    T1.GroupCode,
    T1.Territory,
    T2.Street,
    T2.Block, 
    T2.City, 
    T2.ZipCode, 
    T2.County, 
    T2.State, 
    T2.Country

这是我收到的错误-你可以清楚地看到我在Group By中有字段:

• [Microsoft][SQL Native Client][SQL Server]列"ASAP.dbo.OCRD。区域'在选择列表中无效，因为它是不包含在聚合函数或GROUP BY子句中。
• [Microsoft][SQL Native Client][SQL Server]Statement 'Serv

请告诉

Answer 1

删除Distinct。你已经分组了，所以不需要这个。不确定这是否修复了你的错误。

我也高度怀疑你不希望distinct在你的count()或sum()。您一定是想把所有发票的总数加起来，而不仅仅是那些不一致的吧?使用当前的语法，如果您有两个总额相同的不同发票，则会跳过其中一个。

EDIT:如果您已经从用户传递 Territory中的，为什么您需要在查询中选择它?只需在事实之后将其添加回UI，或者执行:

SELECT ..., [%0] AS Territory, ...