所以我有一堆数据,我已经按照列名和月分组了 . 这是我到目前为止的SQL查询
TestName是列名,POE业务规则/提交每列多次出现VExecutionGlobalHistory是表的名称,Im使用Microsoft SQL Server Management Studio 2010
select
year(dateadd(mm,datediff(mm,0,StartTime),0)),
datename(month,dateadd(mm,datediff(mm,0,StartTime),0)),TestName,
Case WHEN Testname = 'POE Business Rules' THEN (count(TestName)*36) WHEN TestName = 'Submit' THEN (count(TestName)*6) ELSE 0 END
From VExecutionGlobalHistory
group by
year(dateadd(mm,datediff(mm,0,StartTime),0)),
datename(month,dateadd(mm,datediff(mm,0,StartTime),0)),TestName
这个查询给了我这种格式
2013 |APRIL| POE Business Rules| 1044
2013 |APRIL| SUBMIT | 96
2013 |JULY | POE Business Rules| 216
2013 |JULY | SUBMIT | 102
我想有一个最终格式,它只有每个月的计数总和
2013|APRIL|SUM of the counts or (1044 + 96)
2013|JULY |SUM of the counts or (216 + 102)
我不需要testname只是每月计数的总和
我试过在案例之前添加SUM,但我得到 "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
错误 .
对另一种方法的任何建议?
3 回答
你可以使用子查询:
更新:添加ORDER BY以按年份/月份排序 .
就像是
应该这样做
您的逻辑可能过于复杂 . 听起来你只想要按(年,月)分组的加权和 . 具有“POE业务规则”的行值为36,具有“提交”的行值为6,其他行值为0 .