聚合函数错误 [英] Aggregate function error

问题描述

我有以下代码，我想根据他们的小组显示DocStatus。假设我有以下数据：



CustName DocStatus

-------- ---------

John Single

Mary Single

John Married

Mary离婚



我的SQL语句如下

  SELECT  P.DocStatus，C.CustomerName 
  FROM  vsCustomizationRequest.dbo.CRF_Project P，vsCustomizationRequest.dbo.CustomerList C 
  WHERE  C.CustomerName  IN （ SELECT 值来自 funcListToTableInt2（ @ name ，' ，'）） AND  P.CustomerID = C.CustomerID 
  GROUP   BY  C.CustomerNam我想要的输出如下：
 
 
 
 CustName DocStatus 
 
 -------- --------- 
 
 John Single 
 
已婚
 
 Mary Single 
 
离婚
 
 
 
我怎样才能成功？
 
 
 
当我运行SQL语句时它给我错误列在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。，我知道我必须添加一些聚合函数或添加Group By子句中的P.DocStatus但这将显示我不想要的不同输出。任何帮助将不胜感激。
解决方案
 
试试这个
 
 
 
 
 < span class =code-keyword> SELECT  
 CustName 
，st = STUFF（（ SELECT  ' ，' + DocStatus  FROM  yourTable  WHERE  CustName = A.CustName  FOR  XML PATH（' ））， 1 ， 1 ，' '）
  FROM  
 yourTable A 
  GROUP   BY  CustName 
 
 
 
 
 
 
希望这会对你有帮助。
 
 
Hi, i have the following codes in which i wanna display the DocStatus according to their group. Suppose i have the following data:



CustName   DocStatus

--------   ---------

John       Single

Mary       Single

John       Married

Mary       Divorced



my SQL statement is as below"



SELECT P.DocStatus, C.CustomerName
FROM vsCustomizationRequest.dbo.CRF_Project P, vsCustomizationRequest.dbo.CustomerList C
WHERE C.CustomerName IN (SELECT Value from funcListToTableInt2(@name, ',')) AND P.CustomerID = C.CustomerID
GROUP BY C.CustomerName




the output i want is as below:



CustName   DocStatus

--------   ---------

John       Single

           Married

Mary       Single

           Divorced



how can i make it possible?



when i run the SQL statement it give me the error "Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.", i know i have to add some aggregate function or add the P.DocStatus in Group By clause but this will display different output which i dont want. Any help would be appreciated.
解决方案
Try This



SELECT
   CustName
  ,st=STUFF((SELECT ','+DocStatus FROM yourTable WHERE CustName=A.CustName FOR XML PATH('')) , 1 , 1 , '' )
FROM
   yourTable A
GROUP BY CustName






Hope this will help you.


                        
这篇关于聚合函数错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！