CODEWITHSUNDEEP
sqlsql-serversql-server-2008
sharan kumar
sharan kumar

Reputation: 49

Getting error while sum of columns with alias Names

I'm new to SQL. I have a table called management. I'm trying to write a query like below:

SELECT
    registerd, Appeared, registerd + Appeared AS col3 
FROM
    (SELECT 
         COUNT(REGDNO) AS registerd, branch_code,
         SUM(CASE WHEN SUB1_GRADE <> 'Ab' AND SUB1_GRADE IS NOT NULL OR
                       SUB2_GRADE <> 'Ab' AND SUB1_GRADE IS NOT NULL OR
                       SUB2_GRADE <> 'Ab ' AND SUB2_GRADE IS NOT NULL OR
                       SUB3_GRADE <> 'Ab' AND SUB3_GRADE IS NOT NULL OR 
                       SUB4_GRADE <> 'Ab' AND SUB4_GRADE IS NOT NULL OR  
                       SUB5_GRADE <> 'Ab' AND SUB5_GRADE IS NOT NULL OR 
                       SUB6_GRADE <> 'Ab' AND SUB6_GRADE IS NOT NULL OR 
                       SUB7_GRADE <> 'Ab' AND SUB7_GRADE IS NOT NULL
                 THEN 1 ELSE 0 END) As Appeared
     FROM 
         MANAGEMENT) AS subQueryAlias 
GROUP BY
    BRANCH_CODE

but I am getting the following error

Msg 8120, Level 16, State 1, Line 5
Column 'MANAGEMENT.BRANCH_CODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Upvotes: 2

Views: 79

Answers (1)

Mureinik
Mureinik

Reputation: 311393

The group by clause should be part of the inner subquery, not the outer query:

SELECT  registered, appeared, registered + appeared AS col3 
FROM    (SELECT   branch_code,
                  COUNT(regdno) AS registered,
                  SUM(CASE WHEN sub1_grade <> 'Ab' AND sub1_grade IS NOT NULL OR 
                                sub2_grade <> 'Ab' AND sub1_grade IS NOT NULL OR 
                                sub2_grade <> 'Ab' AND sub2_grade IS NOT NULL OR 
                                sub4_grade <> 'Ab' AND sub3_grade IS NOT NULL OR 
                                sub4_grade <> 'Ab' AND sub4_grade IS NOT NULL OR 
                                sub5_grade <> 'Ab' AND sub5_grade IS NOT NULL OR 
                                sub6_grade <> 'Ab' AND sub6_grade IS NOT NULL OR
                                sub7_grade <> 'Ab' AND sub7_grade IS NOT NULL
                           THEN 1 
                           ELSE 0
                      END) AS appeared
         FROM     management
         GROUP BY branch_code) AS subQueryAlias

Upvotes: 2

Related Questions