On Wednesday, April 1, 2020, David Gauthier <
davegauthierpg@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
David J.
