Sum a column and perform more calculations on the result?

Question

In my query below I am counting occurrences in a table based on the Status column. I also want to perform calculations based on the counts I am returning. For example, let's say I want to add 100 to the Snoozed value... how do I do this? Below is what I thought would do it:

SELECT 
    pu.ID Id, pu.Name Name,
    COUNT(*) LeadCount, 
    SUM(CASE WHEN Status = 'Working' THEN 1 ELSE 0 END) AS Working,
    SUM(CASE WHEN Status = 'Uninterested' THEN 1 ELSE 0 END) AS Uninterested,
    SUM(CASE WHEN Status = 'Converted' THEN 1 ELSE 0 END) AS Converted,
    SUM(CASE WHEN SnoozedId > 0 THEN 1 ELSE 0 END) AS Snoozed,
    Snoozed + 100 AS Test
FROM
    Prospects p
INNER JOIN 
    ProspectsUsers pu on p.OwnerId = pu.SalesForceId
WHERE 
    p.Store = '108'
GROUP BY 
    pu.Name, pu.Id
ORDER BY 
    Name

I get this error:

Invalid column name 'Snoozed'.

How can I take the value of the previous SUM statement, add 100 to it, and return it as another column? What I was aiming for is an additional column labeled Test that has the Snooze count + 100.

The error is clear, do this:`100 + SUM(CASE WHEN SnoozedId > 0 THEN 1 ELSE 0 END) AS Test` — forpas, May 06 '19 at 19:40
`Snoozed` is an alias, not a column name. You can't use it, so instead of `Snoozed + 100 AS Test` use `SUM(CASE WHEN SnoozedId > 0 THEN 1 ELSE 0 END) + 100 AS Test` — Ilyes, May 06 '19 at 19:43
The accepted answer of the question this is flagged a duplicate of does not contain valid SQL server syntax. @TabAlleman — Cowthulhu, May 06 '19 at 19:56
@Cowthulhu oops, I used a MySQL duplicate. I have corrected it to point to a TSQL duplicate. — Tab Alleman, May 06 '19 at 19:57

score 1 · Answer 1 · answered May 06 '19 at 19:43

1

You can't use one column to create another column in the same way that you are attempting. You have 2 options:

Do the full calculation (as @forpas has mentioned in the comments above)
Use a temp table or table variable to store the data, this way you can get the first 5 columns, and then you can add the last column or you can select from the temp table and do the last column calculations from there.

answered May 06 '19 at 19:43

dmoore1181

1,793
1
25
57

1

_"You can't use one column to create another column"_ That's wrong. `Snoozed` is an alias not a column name, and you can use a column to create another one in the `SELECT` statement. – Ilyes May 06 '19 at 19:48

score 1 · Answer 2 · edited May 06 '19 at 20:05

You can not use an alias as a column reference in the same query. The correct script is:

SELECT 
    pu.ID Id, pu.Name Name,
    COUNT(*) LeadCount, 
    SUM(CASE WHEN Status = 'Working' THEN 1 ELSE 0 END) AS Working,
    SUM(CASE WHEN Status = 'Uninterested' THEN 1 ELSE 0 END) AS Uninterested,
    SUM(CASE WHEN Status = 'Converted' THEN 1 ELSE 0 END) AS Converted,
    SUM(CASE WHEN SnoozedId > 0 THEN 1 ELSE 0 END)+100 AS Snoozed
FROM
    Prospects p
INNER JOIN 
    ProspectsUsers pu on p.OwnerId = pu.SalesForceId
WHERE 
    p.Store = '108'
GROUP BY 
    pu.Name, pu.Id
ORDER BY 
    Name

score 0 · Answer 3 · edited May 06 '19 at 19:50

You can't use a column alias in the same select. The column alias do not precedence / sequence; they are all created after the eval of the select result, just before group by and order by.

You must repeat code :

SELECT 
    pu.ID Id,pu.Name Name,
    COUNT(*) LeadCount, 
    SUM(CASE WHEN Status = 'Working' THEN 1 ELSE 0 END) AS Working,
    SUM(CASE WHEN Status = 'Uninterested' THEN 1 ELSE 0 END) AS Uninterested,
    SUM(CASE WHEN Status = 'Converted' THEN 1 ELSE 0 END) AS Converted,
    SUM(CASE WHEN SnoozedId > 0 THEN 1 ELSE 0 END) AS Snoozed,
    SUM(CASE WHEN SnoozedId > 0 THEN 1 ELSE 0 END)+ 100 AS Test
FROM 
    Prospects p
INNER JOIN 
    ProspectsUsers pu on p.OwnerId = pu.SalesForceId
WHERE 
    p.Store = '108'
GROUP BY 
    pu.Name, pu.Id
ORDER BY    
    Name

If you don't want to repeat the code, use a subquery

SELECT
    ID, Name, LeadCount, Working, Uninterested,Converted, Snoozed, Snoozed +100 AS test  
FROM
    (SELECT 
          pu.ID Id,pu.Name Name,
            COUNT(*) LeadCount, 
            SUM(CASE WHEN Status = 'Working' THEN 1 ELSE 0 END) AS Working,
            SUM(CASE WHEN Status = 'Uninterested' THEN 1 ELSE 0 END) AS Uninterested,
            SUM(CASE WHEN Status = 'Converted' THEN 1 ELSE 0 END) AS Converted,
            SUM(CASE WHEN SnoozedId > 0 THEN 1 ELSE 0 END) AS Snoozed

      FROM Prospects p
      INNER JOIN ProspectsUsers pu on p.OwnerId = pu.SalesForceId
      WHERE p.Store = '108'
      GROUP BY pu.Name, pu.Id) t 
   ORDER BY Name

or a view

Cowthulhu · Accepted Answer · 2019-05-06T20:58:20.167

MSSQL does not allow you to reference fields (or aliases) in the SELECT statement from within the same SELECT statement.

To work around this:

Use a CTE. Define the columns you want to select from in the CTE, and then select from them outside the CTE.

;WITH OurCte AS (
SELECT
    5 + 5 - 3 AS OurInitialValue
)

SELECT
    OurInitialValue / 2 AS OurFinalValue
FROM OurCte

Use a temp table. This is very similar in functionality to using a CTE, however, it does have different performance implications.

SELECT
    5 + 5 - 3 AS OurInitialValue
INTO #OurTempTable

SELECT
    OurInitialValue / 2 AS OurFinalValue
FROM #OurTempTable

Use a subquery. This tends to be more difficult to read than the above. I'm not certain what the advantage is to this - maybe someone in the comments can enlighten me.

SELECT
    5 + 5 - 3 AS OurInitialValue
FROM (
    SELECT
        OurInitialValue / 2 AS OurFinalValue
) OurSubquery

Embed your calculations. opinion warning This is really sloppy, and not a great approach as you end up having to duplicate code, and can easily throw columns out-of-sync if you update the calculation in one location and not the other.

SELECT
    5 + 5 - 3 AS OurInitialValue
    , (5 + 5 - 3) / 2 AS OurFinalValue

Sum a column and perform more calculations on the result?

4 Answers4