Re: Why oh why is this join not working?

On Mon, 18 Nov 2019 at 14:16, Pól Ua Laoínecháin <linehanp@tcd.ie> 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 t1
> GROUP BY t1.class, t1.roll_number
> JOIN    <<<<<==== Fails here
> (
>   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;
>
> 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.

You could also look into the aggregate FILTER (WHERE ...) clause so
that you don't have to have those not so nice CASE expressions inside
the aggregate. However, I'm unsure what MySQL supports there. You
might be stuck with them if you must support both using the same SQL
syntax.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services