Group by the Sub-Query which is derived from WITH CLAUSE

Question

I'm getting error for my below query, while I'm trying to grouping the sub-query. I built the sub-query using the WITH Clause.

Please correct me

WITH Student AS (SELECT * FROM CLASS WHERE SEX='M')
SELECT NAME, AGE, STATUS, SUM(TOTAL)
(SELECT 
NAME, 
'15' AS AGE,
CASE WHEN ATTENDANCE > 50 AND ATTENDANCE  < 60 THEN 'GOOD' 
WHEN ATTENDANCE > 60 'GREAT'
ELSE 'BAD' END AS STATUS
SUM (MARK) AS TOTAL
FROM STUDENT
GROUP BY NAME, ATTENDANCE ) A
GROUP BY NAME, AGE, STATUS

Error: SQL Query not properly ended

tl;dr you actually need to group by _exactly_ what is in the SELECT statement, i.e. the CASE statement itself. — Ben, Sep 16 '15 at 13:08

score 1 · Answer 1 · answered Sep 16 '15 at 12:15

I think you are missing a from clause:

WITH Student AS (SELECT * FROM CLASS WHERE SEX='M')
SELECT NAME, AGE, STATUS, SUM(TOTAL)
FROM (SELECT NAME, '15' AS AGE,
             (CASE WHEN ATTENDANCE > 50 AND ATTENDANCE  < 60 THEN 'GOOD' 
                   WHEN ATTENDANCE > 60 'GREAT'
                   ELSE 'BAD'
              END) AS STATUS,
             SUM(MARK) AS TOTAL
      FROM STUDENT
      GROUP BY NAME, ATTENDANCE
     ) A
GROUP BY NAME, AGE, STATUS

The query is much longer than it needs to be to achieve the output you want, but this seems to be the problem you have.

Group by the Sub-Query which is derived from WITH CLAUSE

1 Answers1