Invalid Column Name Error when using Sum Case function in SQL

Question

I am trying to sum all case where the field name [Unique] is > 1. I am using Sum case statement and the column [Unique] is not picking up, I also tried to paste the code of unique in and that did not work.

SELECT DSID, SRC_START_DTTM, SRC_END_DTTM
, DWID + CONVERT(float, FORMAT(SRC_END_DTTM, N'yyyyMMddHH'))/10000000000 AS [~Project]
, 
ROW_NUMBER()
OVER (
PARTITION BY DWID + CONVERT(float, FORMAT(SRC_END_DTTM, N'yyyyMMddHH'))/10000000000
ORDER BY SRC_END_DTTM DESC, SRC_START_DTTM DESC
) AS [UNIQUE], 
SUM(
    CASE
    WHEN [UNIQUE] > 1 THEN 1
    ELSE 0
    END) as Test
FROM F.DIM
GROUP BY DSID, SRC_START_DTTM, SRC_END_DTTM, DWID
ORDER BY [UNIQUE] DESC

ERROR: Invalid Column Name 'Unique'

Answer 1

You are trying to use aliased name of a column in same query which is not possible

Try below

    SELECT 
    T.DSID, T.SRC_START_DTTM, T.SRC_END_DTTM, T.DWID
    ,SUM(
    CASE
    WHEN T.[UNIQUE] > 1 THEN 1
    ELSE 0
    END) as Test
FROM
(SELECT DSID, SRC_START_DTTM, SRC_END_DTTM
, DWID + CONVERT(float, FORMAT(SRC_END_DTTM, N'yyyyMMddHH'))/10000000000 AS [~Project]
, ROW_NUMBER()
OVER (
PARTITION BY DWID + CONVERT(float, FORMAT(SRC_END_DTTM, N'yyyyMMddHH'))/10000000000
ORDER BY SRC_END_DTTM DESC, SRC_START_DTTM DESC
) AS [UNIQUE]
FROM F.DIM) AS T
GROUP BY T.DSID, T.SRC_START_DTTM, T.SRC_END_DTTM, T.DWID
ORDER BY T.[UNIQUE] DESC