> 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
onlyexists after the join when the sum(case...) statements are being evaluated. t1.class, however, is part of the
resultdatatable.
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
confidentialand/or legally privileged information. If you are not the intended recipient of this message or if this
messagehas been addressed to you in error, please immediately alert the sender by reply e-mail and then delete this
messageand 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.