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