0

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'

enter image description here

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
MDL7833
  • 1
  • 3
  • Try to change ``AS [UNIQUE]`` to something else like ``AS [UNIQUE_alt]`` – sa-es-ir Apr 13 '22 at 19:38
  • Does this answer your question? [Reference an alias elsewhere in the SELECT list](https://stackoverflow.com/questions/11975749/reference-an-alias-elsewhere-in-the-select-list) – Stu Apr 13 '22 at 19:48

1 Answers1

0

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
Akash Patel
  • 239
  • 1
  • 14
  • It worked, where can I go to read more about what I tried and interactions. ("You are trying to use aliased name of a column in same query which is not possible"). I am still new to using SQL and would like to better understand. Thank you. – MDL7833 Apr 13 '22 at 20:21
  • If you want to know more about aliases then I found this simplest video https://www.youtube.com/watch?v=ngPeJ06f4-A just to explain a bit you were creating a temporary column name using alias "Unique" and you were using it at the same time. – Akash Patel Apr 14 '22 at 09:00