聚合函数错误 [英] 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屋!