On Wednesday, April 1, 2020, David Gauthier <davegauthie...@gmail.com> wrote:
> psql (9.6.7, server 11.3) on linux > > I want to do something like this > (intentionally bad sql but will illustrate the need) > > select s.name,s.grade from students s where s.class='math' > union > select 'whole class', class_grade from all_classes where class=s.class > > Of course it's that "where class=s.class" that's a foul. > > In English, I want a list of each math student and their grade and then > append one more record for the entire class, a record that comes from a > different table but narrowed down to that one class. > > I don't care if union isn't the right approach. Anything that works is > welcome ! > You are correct this model seems undesirable but given you have it why doesn’t the following work for you? Select students where class = ‘math’ Union all Select all_classes where class = ‘math’ The generally better query is: Select from students ... group by rollup https://www.postgresql.org/docs/9.6/queries-table-expressions.html#QUERIES-GROUPING-SETS David J.
