RE: Why oh why is this join not working?

> Now, I can refer to t1.class in the ON clause, but *_not_* to
> t1.englmark. To me this makes no sense - if I can refer to one, I
> should be able to refer to the other?

The error is occurring because t1.englmark doesn’t exist yet at the time that the join is being processed. It would only exists after the join when the sum(case...) statements are being evaluated. t1.class, however, is part of the resultdata table.

Your cte solution in your SQL fiddle is clean and works, so go with that.

EXPLAIN (ANALYZE, BUFFERS) can be used to examine relative performance of different versions of the same query.

Dan
E-MAIL CONFIDENTIALITY NOTICE:
The contents of this e-mail message and any attachments are intended solely for the addressee(s) and may contain confidential and/or legally privileged information. If you are not the intended recipient of this message or if this message has been addressed to you in error, please immediately alert the sender by reply e-mail and then delete this message and any attachments. If you are not the intended recipient, you are notified that any use, dissemination, distribution, copying, or storage of this message or any attachment is strictly prohibited.