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

Try putting PROC in square brackets like this [PROC] or use a different word PROC may be a reserved word or a reserved abbreviation for PROCEDURE. — Duston, Apr 08 '21 at 12:56
Depending on the DBMS you are using. You cant sum columns that are aliased like that, you would have to use a sub select and do the sum from there. If you verify your DBMS we can create query — Brad, Apr 08 '21 at 12:59

score 1 · Accepted Answer · answered Apr 08 '21 at 13:00

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;

score 1 · Answer 2 · answered Apr 08 '21 at 13:01

Depending on the DBMS you are using. You cant sum columns that are aliased like that, you would have to use a sub select and do the sum from there. If you verify your DBMS we can create query.

If MS SQL the below will work. A couple things:

PROC is reserved word, so either change that or put brackets around it (I went for brackets). Also it is preferred if you use JOINS vs. the way you had the queries.

SELECT OPD, IPD, DC, [PROC], SUR, (OPD + IPD + [PROC]) as Total
FROM (
    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
    FROM REF_TB_APP_TRANSACTIONS A
    INNER JOIN REF_VW_VISIT_TYPE B ON A.REQ_VISIT_TYPE = B.ID
    WHERE A.TO_EST_CODE = 20068
) SUB

score 1 · Answer 3 · answered Apr 08 '21 at 14:29

You can't reference the aliased columns as part of the select because in the order of query execution, they don't exist yet.

You simply wrap your query so it becomes a derived table and then you can refer to them in an outer select, see:

select OPD, IPD, DC, [PROC], SUR, OPD + IPD + [PROC] as Total from (
    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
    FROM REF_TB_APP_TRANSACTIONS A
    join REF_VW_VISIT_TYPE B on B.ID=A.REQ_VISIT_TYPE
    where A.TO_EST_CODE = 20068
)x

Guessing because you have a semi-colon this is SQLServer, in which case you will need to use [] around the reserved word PROC

I've also properly joined your tables as it's not 1989 any more :-0

invalid identifier : sum of multiple column in sql

3 Answers3