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/