invalid identifier : sum of multiple column in sql

Question

I'm trying to calculate multible columns in this query

     SELECT
         SUM (CASE WHEN B.ID = 1 THEN 1 END)     AS OPD,
         SUM (CASE WHEN B.ID = 2 THEN 1 END)     AS IPD,
         SUM (CASE WHEN B.ID = 3 THEN 1 END)     AS DC,
         SUM (CASE WHEN B.ID = 4 THEN 1 END)     AS PROC,
         SUM (CASE WHEN B.ID = 5 THEN 1 END)     AS SUR,
(OPD + IPD + PROC) as Total
    FROM REF_TB_APP_TRANSACTIONS A,
         REF_VW_VISIT_TYPE      B
        
   WHERE     A.REQ_VISIT_TYPE = B.ID
         AND A.TO_EST_CODE = 20068;

but I got this error PROC invalid identifier

Answer 1

You can't add the three SUMS in the Total column in the SELECT directly, since you're using the aliases of those columns. You could just do your Total column with another SUM CASE.

SELECT
    SUM (CASE WHEN B.ID = 1 THEN 1 END)     AS OPD,
    SUM (CASE WHEN B.ID = 2 THEN 1 END)     AS IPD,
    SUM (CASE WHEN B.ID = 3 THEN 1 END)     AS DC,
    SUM (CASE WHEN B.ID = 4 THEN 1 END)     AS [PROC],
    SUM (CASE WHEN B.ID = 5 THEN 1 END)     AS SUR,
    SUM (CASE WHEN B.ID IN (1,2,4)THEN 1 END)     AS Total
FROM REF_TB_APP_TRANSACTIONS A,
        REF_VW_VISIT_TYPE      B
WHERE     A.REQ_VISIT_TYPE = B.ID
        AND A.TO_EST_CODE = 20068;