如何SQL - 在别名列名中使用聚合函数SUM

How to calculate the sum of column alias "Performance_Indicators". I want to total the sum of Performance_Indicator. I cannot use SUM(Performance_Indicators).

如何计算列别名“Performance_Indicators”的总和。我想总计Performance_Indicator的总和。我不能使用SUM（Performance_Indicators）。

SELECT    a.username
          ,a.name
          ,a.description
          ,a.action_header 
          ,a.remarks
          ,e.complexity
          ,datediff(DAY, e.entry_date, a.datetime_stamp) as [man-days]

          ,CASE 
            WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
            = 0  THEN '1'
            WHEN e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
            >= 29 THEN '2'
            WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
            >= 14 THEN '3'
            WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
            >= 8 THEN '4'
            WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
            <= 7  THEN '5'

            ELSE '1'

         END AS Performance_Indicators,
         SUM(Performance_Indicators) as total



     from [s].[dbo].[tbl_de] a
        left join 
        [s].[dbo].[tbl_ce] e
        on a.reference_code = e.reference_code

         order by a.name asc

3 个解决方案

#1

Change the CASE statement to yield integers rather than strings and then use SUM(CASE...END) as Performance_Indicators.

更改CASE语句以生成整数而不是字符串，然后使用SUM（CASE ... END）作为Performance_Indicators。

#2

Try this:

尝试这个：

SELECT    a.username
      ,a.name
      ,a.description
      ,a.action_header 
      ,a.remarks
      ,e.complexity
      ,datediff(DAY, e.entry_date, a.datetime_stamp) as [man-days]

      ,SUM(CASE 
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        = 0  THEN 1
        WHEN e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        >= 29 THEN 2
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        >= 14 THEN 3
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        >= 8 THEN 4
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        <= 7  THEN 5
        ELSE 1
     END) AS total
 from [s].[dbo].[tbl_de] a
    left join 
    [s].[dbo].[tbl_ce] e
    on a.reference_code = e.reference_code
GROUP BY a.username
      ,a.name
      ,a.description
      ,a.action_header 
      ,a.remarks
      ,e.complexity
      ,datediff(DAY, e.entry_date, a.datetime_stamp)
order by a.name asc

#3

To achieve that, you should subquery on first sentence, then make main query to sum it.

要实现这一点，你应该在第一个句子上查询，然后使主查询对它求和。

Try this

尝试这个

WITH CTE AS
(
SELECT    a.username
      ,a.name
      ,a.description
      ,a.action_header 
      ,a.remarks
      ,e.complexity
      ,datediff(DAY, e.entry_date, a.datetime_stamp) as [man-days]
      ,CASE 
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        = 0  THEN 1
        WHEN e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        >= 29 THEN 2
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        >= 14 THEN 3
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        >= 8 THEN 4
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        <= 7  THEN 5
        ELSE 1
     END AS PerformanceIndicator
 from [s].[dbo].[tbl_de] a
    left join 
    [s].[dbo].[tbl_ce] e
      on a.reference_code = e.reference_code
)

SELECT    a.username
      ,a.name
      ,a.description
      ,a.action_header 
      ,a.remarks
      ,e.complexity
      ,datediff(DAY, e.entry_date, a.datetime_stamp) as [man-days]
      ,cte.PerformanceIndicator
      ,SUM(CASE 
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        = 0  THEN 1
        WHEN e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        >= 29 THEN 2
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        >= 14 THEN 3
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        >= 8 THEN 4
        WHEN  e.complexity = 'Simple'  AND datediff(DAY, e.entry_date, a.datetime_stamp)
        <= 7  THEN 5
        ELSE 1
     END) AS total
 from [s].[dbo].[tbl_de] a
    left join [s].[dbo].[tbl_ce] e on a.reference_code = e.reference_code
    inner join cte on cte.name = a.name and cte.description = a.description
GROUP BY a.username
      ,a.name
      ,a.description
      ,a.action_header 
      ,a.remarks
      ,e.complexity
      ,datediff(DAY, e.entry_date, a.datetime_stamp)
      ,cte.PerformanceIndicator
order by a.name asc

#1