On 11/17/19 8:15 PM, Pól Ua Laoínecháin wrote:
> BUT, when I try and run this (JOINING the two tables above):
>
> 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
>
Hello Pól,
I think you just need parentheses around the first subquery, and to put
both subqueries in the FROM list of an outer SELECT.
When I run this, I get the same result as your CTE query:
SELECT t1.class, t1.roll_number, t1.mathmark, t1.englmark
FROM
(
SELECT class, roll_number,
SUM(CASE WHEN subjects = 'math'
THEN marks ELSE 0 END) AS mathmark,
SUM(CASE WHEN subjects = 'computer'
THEN marks ELSE 0 END) AS compmark,
SUM(CASE WHEN subjects = 'english'
THEN marks ELSE 0 END) AS englmark,
SUM(CASE WHEN subjects = 'science'
THEN marks ELSE 0 END) AS sciemark
FROM resultdata
GROUP BY class, 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 ASC;
Regards,
Bryan
--
Postdoctoral Researcher
Georgia Cooperative Fish & Wildlife Research Unit
Warnell School of Forestry & Natural Resources
University of Georgia
Athens, GA 30606-2152
