Re: Why oh why is this join not working?

On Mon, 2019-11-18 at 07:56 +0000, Pól Ua Laoínecháin wrote:
> SELECT t1.class, t1.roll_number,
>   SUM(CASE WHEN t1.subjects = 'math'
>     THEN t1.marks ELSE 0 END) AS mathmark,
>   SUM(CASE WHEN t1.subjects = 'computer'
>     THEN t1.marks ELSE 0 END) AS compmark,
>   SUM(CASE WHEN t1.subjects = 'english'
>     THEN t1.marks ELSE 0 END)  AS englmark,
>   SUM(CASE WHEN t1.subjects = 'science'
>     THEN t1.marks ELSE 0 END)  AS sciemark
> FROM resultdata AS t1
> JOIN
> (
>   SELECT class, MAX(marks) AS maxmark
>   FROM resultdata
>   WHERE subjects = 'english'
>   GROUP BY class
> ) AS t2
> ON t1.class = t2.class
> AND t1.englmark = t2.maxmark        -- <<<<<====== Error here! I can
> refer to t1.class, but not t1.englmark - why?
> GROUP BY t1.class, t1.roll_number
> ORDER BY class, englmark DESC;
> 
> but this gives the error:
> 
> ERROR: column t1.englmark does not exist LINE 19: AND t1.englmark = t2.maxmark

You cannot refer to a column alias in a WHERE condition,
because grouping takes place *after* the WHERE condition
has been evaluated.

Try to use a subquery:

SELECT t1.class, t1.roll_number,
       t1.mathmark,
       t1.compmark,
       t1.englmark,
       t1.sciemark
FROM (SELECT class, roll_number,
             SUM(t1.marks) FILTER (WHERE t1.subjects = 'math') AS mathmark,
             SUM(t1.marks) FILTER (WHERE t1.subjects = 'computer') AS compmark,
             SUM(t1.marks) FILTER (WHERE t1.subjects = 'english') AS englmark,
             SUM(t1.marks) FILTER (WHERE t1.subjects = 'science') AS sciemark
      FROM resultdata
      GROUP BY t1.class, t1.roll_number
     ) AS t1
   JOIN (SELECT class, MAX(marks) AS maxmark
         FROM resultdata
         WHERE subjects = 'english'
         GROUP BY class
        ) AS t2
      ON t1.class = t2.class AND t1.englmark = t2.maxmark
ORDER BY class, englmark DESC;

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com