首页 文章

SQL Sum(Count)的特定列和组SUM按月

提问于
浏览
0

所以我有一堆数据,我已经按照列名和月分组了 . 这是我到目前为止的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 回答

  • 3

    你可以使用子查询:

    SELECT Year_, Month_, SUM(Counts)
    FROM (
            SELECT YEAR(DATEADD(MM,DATEDIFF(MM,0,StartTime),0))'Year_'
                  ,DATENAME(MONTH,DATEADD(MM,DATEDIFF(MM,0,StartTime),0))'Month_'
                  ,TestName
                  ,CASE WHEN Testname = 'POE Business Rules' THEN (count(TestName)*36) 
                        WHEN TestName = 'Submit' THEN (COUNT(TestName)*6) 
                        ELSE 0 
                   END 'Counts'
            FROM VExecutionGlobalHistory
            GROUP BY YEAR(DATEADD(MM,DATEDIFF(MM,0,StartTime),0))
                    ,DATENAME(MONTH,DATEADD(MM,DATEDIFF(MM,0,StartTime),0))
                    ,TestName
            )sub
    GROUP BY Year_, Month_
    ORDER BY CAST(CAST(Year_ AS CHAR(4)) + Month_ + '01' AS DATETIME)
    

    更新:添加ORDER BY以按年份/月份排序 .

  • 0

    就像是

    Select SumYear,SomeMonth,Sum(SumCounts) From
    (
    select  
       year(dateadd(mm,datediff(mm,0,StartTime),0)) as SumYear,
        datename(month,dateadd(mm,datediff(mm,0,StartTime),0)) as SumMonth,TestName,
    
        Case WHEN Testname = 'POE Business Rules' THEN (count(TestName)*36) WHEN TestName =         'Submit' THEN (count(TestName)*6) ELSE 0 END as sumCounts
    
        From VExecutionGlobalHistory
    
        group by
        year(dateadd(mm,datediff(mm,0,StartTime),0)),
        datename(month,dateadd(mm,datediff(mm,0,StartTime),0)),TestName ) sums
    Group by SumYear,SumMonth
    

    应该这样做

  • 0

    您的逻辑可能过于复杂 . 听起来你只想要按(年,月)分组的加权和 . 具有“POE业务规则”的行值为36,具有“提交”的行值为6,其他行值为0 .

    SELECT
      YEAR(StartTime),
      DATENAME(month,DATEADD(month,MONTH(StartTime)-1,0)),
      SUM(
        CASE Testname
          WHEN 'POE Business Rules' THEN 36
          WHEN 'Submit'             THEN 6
          ELSE 0
        END
      )
    FROM VExecutionGlobalHistory
    GROUP BY YEAR(StartTime),MONTH(StartTime)
    ORDER BY YEAR(StartTime),MONTH(StartTime)
    

相关问题