SQL - Informix error leads to another error - hallway of mirrors P.I.T.A

Question

I'm using Informix version 11.50.FC6 via iSql

---

I'm giving the result of a CASE block a virtual name, att_hrs

SELECT      c.id,
            CASE    WHEN    (   c.prog = 'UNDG'
                                AND (c.grd  IN (SELECT DISTINCT grd FROM grd_table WHERE att_fctr = 1) OR (c.grd IN ('TR','W','LAB','WC')))
                                AND c.grd   NOT IN ('WM')
                                AND c.stat  NOT IN ('X','D'))
                    THEN    CAST(SUM(c.hrs) AS CHAR(4))
                    ELSE    'ELSED (att)'
            END     att_hrs 
FROM        cw_rec c
WHERE       c.id IN (SELECT DISTINCT id FROM stu_ids)
GROUP BY    c.id
INTO TEMP   cheese
WITH NO LOG;

This gives me an error:

294: The column (att_hrs) must be in the GROUP BY list.

Trying to fix the error as suggested:

SELECT      c.id,
            CASE    WHEN    (   c.prog = 'UNDG'
                                AND (c.grd  IN (SELECT DISTINCT grd FROM grd_table WHERE att_fctr = 1) OR (c.grd IN ('TR','W','LAB','WC')))
                                AND c.grd   NOT IN ('WM')
                                AND c.stat  NOT IN ('X','D'))
                    THEN    CAST(SUM(c.hrs) AS CHAR(4))
                    ELSE    'ELSED (att)'
            END     att_hrs 
FROM        cw_rec c
WHERE       c.id IN (SELECT DISTINCT id FROM stu_ids)
GROUP BY    c.id,
            att_hrs
INTO TEMP   cheese
WITH NO LOG;

Then gives me this error:

217: Column (att_hrs) not found in any table in the query (or SLV is undefined).

---

They kind of found att_hrs pretty easily when it wasn't in the GROUP BY party, but now all of a sudden, att_hrs is lost in the sauce...

Can you get around this?

What are the real errors &| solutions to what is going on here and what I need to do to fix it?

---

EDIT
I tried RET's solution to GROUP BY 1,2,3... and got the following error:

321: Cannot group by aggregate column.

Answer 1

I took everyone's advice that it isn't possible and rewrote it using UNION blocks.

Table & field name(s) may have varied, but here's the idea:

SELECT          s.id,
                SUM(c.hrs) hrs,
                'ATT' type
FROM            expected_contacts s,
                OUTER stu_crs c
WHERE           s.id = c.id
                AND c.prog = 'UNDG'
                AND c.grd   NOT IN ('WM')
                AND c.stat  NOT IN ('X','D')
                AND (c.grd  IN (SELECT DISTINCT grd 
                                FROM            grd_table 
                                WHERE           att_fctr = 1) 
                            OR (c.grd IN ('TR','W','LAB','WC')))
                AND c.crs_no <> 'PARM101'
GROUP BY        s.id
UNION
SELECT          s.id,
                SUM(c.hrs) hrs,
                'EARN' type
FROM            expected_contacts s,
                OUTER stu_crs c
WHERE           s.id = c.id
                AND c.prog = 'UNDG'
                AND (c.grd <= 'DI' or c.grd like 'S%' or c.grd IN ('P','LAB','TR'))
                AND c.stat NOT IN ('D','W','X')
GROUP BY        s.id
UNION
SELECT          s.id,
                SUM(c.hrs) hrs,
                'DEV' type
FROM            expected_contacts s,
                OUTER stu_crs c
WHERE           s.id = c.id
                AND c.prog = 'UNDG'
                AND (   c.crs_no    LIKE 'ENGL0%' 
                        OR c.crs_no LIKE 'MATH0%' 
                        OR c.crs_no LIKE 'ENGSL0%' 
                        OR c.crs_no LIKE 'ESOL0%')
                AND c.stat IN ('C','R','W')
                AND c.grd <> 'IP'   
GROUP BY        s.id
INTO TEMP       stu_acad
WITH NO LOG;