How to SQL - use of Aggregate function SUM in alias column name

Question

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).

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

Can you also post the error message that you are getting? I am not familiar with the brackets around the query. Is that a Microsoft SQL Server thing? — Huckphin
– Huckphin, Commented Jan 5, 2018 at 1:45
Your query does not make sense as currently written. You have two choices, either just select only the sum of performance indicators, or add GROUP BY to your query and select that sum for each group. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 5, 2018 at 1:48
@Huckphin I get an error Invalid column name 'Performance_Indicators'. — Candy083
– Candy083, Commented Jan 5, 2018 at 1:55
You can't reference an alias created in the SELECT list in other expressions in the SELECT list. Also the query doesn't make sense - you want to return every row but then a sum of that expression across rows? Which rows? Grouped by what? You could use a CTE to first expose the Performance_Indicators and then SUM but you need to resolve other logic issues with your query first. I suggest instead of posting your non-working query, you post table structure, sample data and desired results, then you might get a more appropriate and optimal query to get your desired results. — Aaron Bertrand
– Aaron Bertrand, Commented Jan 5, 2018 at 2:21
In addition, there is an extra coma before the first select field (a.username). — kc2018
– kc2018, Commented Jan 5, 2018 at 2:25

rd_nielsen · Accepted Answer · 2018-01-05 01:45:10Z

1

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

answered Jan 5, 2018 at 1:45

rd_nielsen

2,4592 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Candy083 Over a year ago

how to change the case statement to yield integers? do i need to use cast? or convert?

Aaron Bertrand Over a year ago

@Candy083 You change THEN '1' to THEN 1, for example. e.g. no quotes.

Candy083 Over a year ago

Yes I did that but still get the same output as Performance_indicator column , it do not get the sum

Aaron Bertrand Over a year ago

@Candy083 With what query? Not the one in the question, because that query won't compile.

Candy083 Over a year ago

I just put the sum(case...end)

DineshDB · Accepted Answer · 2018-01-05 02:36:34Z

1

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

answered Jan 5, 2018 at 2:36

DineshDB

6,2238 gold badges38 silver badges54 bronze badges

4 Comments

Candy083 Over a year ago

I have used that query but still got the same output like 'Performance_Indicators'

DineshDB Over a year ago

@Candy083, can you give some sample data and Expected result?

Parfait Over a year ago

@Candy083, can you give some sample data of current result?

Candy083 Over a year ago

I include the expected result above.

Erwin Antonius · Accepted Answer · 2018-01-05 04:21:35Z

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

Collectives™ on Stack Overflow

How to SQL - use of Aggregate function SUM in alias column name

3 Answers 3

5 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related