Re: Why oh why is this join not working?

Hi David and thanks for your interest and I am happy to report that
some progress has been made!

> > Result is: ERROR: syntax error at or near "JOIN" LINE 12: JOIN

> This isn't valid SQL. GROUP BY logically and syntactically comes after
> joins at each level. If you want to group before join then you can
> include that SQL in a derived table in the FROM clause, e.g:
> SELECT t1.class, t1.roll_number FROM (SELECT SUM(...) FROM resultdata
> t1 GROUP BY t1.class, t1.roll_number) t1 JOIN ....

> Note the additional SELECT and parenthesis around it.

Indeed, and this hint has got me considerably further in this issue!

I am now running this query: fiddle here -
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=97ef20c1981d2d1d4918f1b3599ead32

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

I don't seem to be able to refer to the result(s) of my SUM(CASE...)
in the ON clause of the JOIN? I've tried various solutions to this,
and again, nothing works!

> You could also look into the aggregate FILTER (WHERE ...) clause so

Thanks for the heads up - it would be a perfect use case and I haven't
really looked at that one much. I've been trying to follow Markus
Winand's " Modern SQL in Open Source and Commercial Databases" theme
of not remaining stuck with SQL '92! But, I also have to deal with
MySQL 5.7 (eyes raise to heaven! :-) )

Thanks again for your input! Regards,

Pól...

> David Rowley http://www.2ndQuadrant.com/