SUM by并非GROUP BY的所有字段

Question

我有桌子：

Id(int), 
CompanyId(uniqueidentifier), 
DateCreated(datetime), 
TypeId(int), 
Value(int), 
Date(date)

和查询：

SELECT 
    W2CliendId,
    CAST(DATEADD(month, DATEDIFF(month, 0, Date), 0) AS date) AS MonthStarts,
    CASE 
      WHEN TypeId = 1 THEN '4 Search views'
      WHEN TypeId = 2 THEN '5 Maps views'
      WHEN TypeId = 0 THEN 'Unknown'
    END AS [Type],
    SUM(Value) AS [Value]
FROM CompanyTypeValues CTV
    JOIN CompanyProfile CP
        ON CP.CompanyId = CTV.CompanyId
WHERE CP.Status = 0 AND Date >= '2015-08-11'
GROUP BY CAST(DATEADD(month, DATEDIFF(month, 0, Date), 0) AS date), TypeId, W2CliendId
ORDER BY TypeId, MonthStarts, W2CliendId

如何从GROUP BY中仅对2个字段求和？(DATEADD(month, DATEDIFF(month, 0, Date), 0), W2CliendId)

Answer 1

如果你只有3个类型it (从你的案例中看起来就是上面的总和)，一个解决方案是创建三个重复案例的列，这样你就有3个对应于这三个类型it的聚合结果：

因此，在查询的末尾，将

SUM(Value) AS [Value]

通过

SUM(CASE 
      WHEN TypeId = 1 THEN Value
      WHEN TypeId = 2 THEN 0
      WHEN TypeId = 0 THEN 0
END AS [Value_Type1],
SUM(CASE 
      WHEN TypeId = 1 THEN 0
      WHEN TypeId = 2 THEN Value
      WHEN TypeId = 0 THEN 0
END AS [Value_Type2],
SUM(CASE 
      WHEN TypeId = 1 THEN 0
      WHEN TypeId = 2 THEN 0
      WHEN TypeId = 0 THEN Value
END AS [Value_Type0],

Group by不是为了在同一查询的两个不同级别聚合而设计的。否则，您可以做的是两个单独的子查询，一个聚合在3列上，另一个聚合在2列上，并连接这两个查询。

Answer 2

如果必须按选定的列进行分区，请尝试使用窗口函数

SELECT   w2cliendid, 
         cast(dateadd(month, datediff(month, 0, date), 0) AS date) AS monthstarts, 
         CASE 
                  WHEN typeid = 1 THEN '4 Search views' 
                  WHEN typeid = 2 THEN '5 Maps views' 
                  WHEN typeid = 0 THEN 'Unknown' 
         END                                                                                                                                         AS [Type],
         sum(value) OVER (partion BY cast(dateadd(month, datediff(month, 0, date), 0) AS date)
         , w2cliendid ORDER BY typeid, monthstarts, w2cliendid) AS [Value] 
FROM     companytypevalues ctv 
JOIN     companyprofile cp 
ON       cp.companyid = ctv.companyid 
WHERE    cp.status = 0 
AND      date >= '2015-08-11'