使用多个COUNTS()和一个GROUP BY进行查询

Question

我有一个查询，它返回所有的装运零件、工厂零件、每个零件的输入日期以及由谁用各自的电子邮件输入。但我需要每个人输入的所有船舶零件和工厂零件的总数，所以我添加了下面的count函数，但我一直收到这个错误"每个GROUP BY表达式必须至少包含一个不是外部引用的列。"有什么想法吗？

select Distinct cc.FirstName+' '+cc.LastName AS 'Name', UEmail AS 'Email', sp.SEntered AS 'Date Entered', COUNT(sp.SHIPID) AS 'Shipments',   COUNT(pn.PNumber) AS 'Factory Parts'
from R_USERS_Detail U
join R_SHIPMENTS sp on sp.UID=U.UID
join R_SHIPMENTOrders sd on sd.SHIPID=sp.SHIPID
join R_PARTOrders pn on pn.PNumberID=sd.PNumberID
join R_Clients c on c.COClientID=sp.COClientID AND c.COClientAcronym LIKE '%AME%'
join R_Contacts cc on cc.COContactID=u.COContactID
group by u.UEmail, sp.SEntered, 'Name'

Answer 1

您必须在group by子句中使用精确的表达式，而不是别名。

select cc.FirstName+' '+cc.LastName AS 'Name'
     , UEmail AS 'Email'
     , sp.SEntered AS 'Date Entered'
     , COUNT(sp.SHIPID) AS 'Shipments'
     , COUNT(pn.PNumber) AS 'Factory Parts'
from R_USERS_Detail U
join R_SHIPMENTS sp on sp.UID=U.UID
join R_SHIPMENTOrders sd on sd.SHIPID=sp.SHIPID
join R_PARTOrders pn on pn.PNumberID=sd.PNumberID
join R_Clients c on c.COClientID=sp.COClientID AND c.COClientAcronym LIKE '%AME%'
join R_Contacts cc on cc.COContactID=u.COContactID
group by u.UEmail, sp.SEntered, cc.FirstName+' '+cc.LastName