SQL group by子句错误子查询返回的值超过1个。这是不允许的，当子查询遵循=，！=，

Question

我有查询按组计算活动客户端并且必须按组计算拾取。如果我不使用GROUP BY Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

在4号线它的工作原理，但没有按组划分皮卡：

SELECT [Category] = ISNULL(c.Category, 'Total'), 
     COUNT (hs.ClientID) as '3 Mo Clients', 
     (COUNT (hs.ClientID))/3 as MoAVG 
     ,(select COUNT(PickupID)from Pickup where PickupDate between '2013-01-01' and '2013-03-30' group by CategCode) as '3 Mo Picups' 

FROM Category c 
     LEFT JOIN history hs 
      ON hs.CategCode = c.CategCode 
      where ReccordDate between '2013-01-01' and '2013-03-30' 
GROUP BY Category 
WITH ROLLUP 

这个查询抛出错误。 这就是例子，它是如何工作没有GROUP BY子句中的4行代码

任何想法如何解决呢？

Answer 1

尝试添加此条件：CategCode = c.CategCode

SELECT [Category] = ISNULL(c.Category, 'Total'), 
     COUNT (hs.ClientID) as [3 Mo Clients], 
     (COUNT (hs.ClientID))/3 as MoAVG 
     ,(SELECT COUNT(PickupID) 
     FROM Pickup 
     WHERE PickupDate between '2013-01-01' and '2013-03-30' 
      AND CategCode = c.CategCode 
     ) AS [3 Mo Picups] 
FROM Category c LEFT JOIN history hs ON hs.CategCode = c.CategCode 
WHERE ReccordDate between '2013-01-01' and '2013-03-30' 
GROUP BY c.CategCode, c.Category 
WITH ROLLUP 

OR

SELECT [Category] = ISNULL(c.Category, 'Total'), 
     COUNT (hs.ClientID) as [3 Mo Clients], 
     (COUNT (hs.ClientID))/3 as MoAVG, 
     SUM(o.cnt) AS [3 Mo Picups] 
FROM Category c LEFT JOIN history hs ON hs.CategCode = c.CategCode 
       CROSS APPLY (
          SELECT COUNT(PickupID) AS cnt 
          FROM Pickup 
          WHERE PickupDate between '2013-01-01' and '2013-03-30' 
           AND CategCode = c.CategCode 
          ) o 
WHERE ReccordDate between '2013-01-01' and '2013-03-30' 
GROUP BY c.Category 
WITH ROLLUP