Postgresql – SQL: Summarize rows by column value, without using UNION

I’m not sure if any of the following will work for you. This is something that
I haven’t ever used in a real project, but maybe it will provide you with some
inspiration. The final solution will get 99% to having the best case that you
wanted (more on this at the end).

Here’s the basic idea. psql provides a command called crosstabview that is
able to display pivot tables, which is what you want. So, for your sample data,
we could do this (notice that instead of terminating the query with a semicolon
;, we’re terminating it with crosstabview):

select load_nr, 's0_table1', count(*) from s0_table1 group by load_nr
union
select load_nr, 's0_table2', count(*) from s0_table2 group by load_nr
order by load_nr asc

crosstabview

┌─────────┬───────────┬───────────┐
│ load_nr │ s0_table1 │ s0_table2 │
├─────────┼───────────┼───────────┤
│     101 │         2 │         1 │
│     102 │         1 │         4 │
│     103 │         3 │         1 │
└─────────┴───────────┴───────────┘

Try the same query with a semicolon terminator to see what the query has returned
before psql post-processed the result:

select load_nr, 's0_table1', count(*) from s0_table1 group by load_nr
union
select load_nr, 's0_table2', count(*) from s0_table2 group by load_nr
order by load_nr asc

┌─────────┬───────────┬───────┐
│ load_nr │ ?column?  │ count │
├─────────┼───────────┼───────┤
│     101 │ s0_table1 │     2 │
│     101 │ s0_table2 │     1 │
│     102 │ s0_table1 │     1 │
│     102 │ s0_table2 │     4 │
│     103 │ s0_table2 │     1 │
│     103 │ s0_table1 │     3 │
└─────────┴───────────┴───────┘

Ok. Now this was a psql thing, but it PostgreSQL provides a built-in
extension called tablefunc which exposes a function called crosstab that
does precisely what the psql crosstabview command does. Furthermore, this
function accepts as argument a string containing the SQL text of the query
you need, which means we could generate it.

For the moment we can just use the hardcoded query (I’m using $$-delimited
strings below so that I don’t have to escape single quotes):

create extension if not exists tablefunc;

select * from crosstab(
  -- The 1st argument is the query producing the values on the two dimentions:
  -- `load_nr` and table name.
  $$
  select load_nr, 's0_table1', count(*) from s0_table1 group by load_nr
  union
  select load_nr, 's0_table2', count(*) from s0_table2 group by load_nr
  order by load_nr asc
  $$,

  -- The 2nd argument must return the headers names, which in our case are
  -- the table names
  $$
  select * from (values ('s0_table1'), ('s0_table2')) as headers
  $$
) as (
  "load_nr" int,
  "count s0_table1" int,
  "count s0_table2" int
);

The above will return this, which reproduces perfectly the crosstabview
result:

┌─────────┬─────────────────┬─────────────────┐
│ load_nr │ count s0_table1 │ count s0_table2 │
├─────────┼─────────────────┼─────────────────┤
│     101 │               2 │               1 │
│     102 │               1 │               4 │
│     103 │               3 │               1 │
└─────────┴─────────────────┴─────────────────┘

Ok, now on to dynamically generate the two SQL queries that crosstab wants.

First, we can obtain a list of the table names prefixed with s0_:

select
  relname
from
  pg_catalog.pg_class
where relname like 's0_%'
order by relname asc;

┌───────────┐
│  relname  │
├───────────┤
│ s0_table1 │
│ s0_table2 │
└───────────┘

Now, we can use these values to build individual SELECT statements that we’ll
later join via UNION. First the SELECTs:

select
  'SELECT load_nr, ''' || relname || ''', count(*) FROM ' || relname || ' GROUP BY load_nr' as sql_text
from
  pg_catalog.pg_class
where relname like 's0_%'
order by relname asc;

┌───────────────────────────────────────────────────────────────────────┐
│                               sql_text                                │
├───────────────────────────────────────────────────────────────────────┤
│ SELECT load_nr, 's0_table1', count(*) FROM s0_table1 GROUP BY load_nr │
│ SELECT load_nr, 's0_table2', count(*) FROM s0_table2 GROUP BY load_nr │
└───────────────────────────────────────────────────────────────────────┘
(2 rows)

Now we can join the 2 rows into a single one:

select string_agg(sql_text, E'nUNIONn') || E'nORDER BY load_nr ASC' from (
  select
    'SELECT load_nr, ''' || relname || ''', count(*) FROM ' || relname || ' GROUP BY load_nr' as sql_text
  from
    pg_catalog.pg_class
  where relname like 's0_%'
  order by relname asc
) as queries;

┌───────────────────────────────────────────────────────────────────────┐
│                               ?column?                                │
├───────────────────────────────────────────────────────────────────────┤
│ SELECT load_nr, 's0_table1', count(*) FROM s0_table1 GROUP BY load_nr↵│
│ UNION                                                                ↵│
│ SELECT load_nr, 's0_table2', count(*) FROM s0_table2 GROUP BY load_nr↵│
│ ORDER BY load_nr ASC                                                  │
└───────────────────────────────────────────────────────────────────────┘
(1 row)

If we take this query and manually run it using crosstabview, we’ll get:

SELECT load_nr, 's0_table1', count(*) FROM s0_table1 GROUP BY load_nr
UNION
SELECT load_nr, 's0_table2', count(*) FROM s0_table2 GROUP BY load_nr
ORDER BY load_nr ASC crosstabview

┌─────────┬───────────┬───────────┐
│ load_nr │ s0_table1 │ s0_table2 │
├─────────┼───────────┼───────────┤
│     101 │         2 │         1 │
│     102 │         1 │         4 │
│     103 │         3 │         1 │
└─────────┴───────────┴───────────┘
(3 rows)

Nice, so this was precisely what we wanted. We can now use SQL-generating query
as the first argument to the function crosstab:

with
  source_sql as (
    select string_agg(sql_text, E'nUNIONn') || E'nORDER BY load_nr ASC' from (
      select
        'SELECT load_nr, ''' || relname || ''', count(*) FROM ' || relname || ' GROUP BY load_nr' as sql_text
      from
        pg_catalog.pg_class
      where relname like 's0_%'
      order by relname asc
    ) as queries
  )
  select * from crosstab(
    -- The 1st argument is the query producing the values on the two dimentions:
    -- `load_nr` and table name.
    (select * from source_sql),

    -- The 2nd argument must return the headers names, which in our case are
    -- the table names
    $$
    select * from (values ('s0_table1'), ('s0_table2')) as headers
    $$
  ) as (
    "load_nr" int,
    "count s0_table1" int,
    "count s0_table2" int
  );

Executing it will get us:

┌─────────┬─────────────────┬─────────────────┐
│ load_nr │ count s0_table1 │ count s0_table2 │
├─────────┼─────────────────┼─────────────────┤
│     101 │               2 │               1 │
│     102 │               1 │               4 │
│     103 │               3 │               1 │
└─────────┴─────────────────┴─────────────────┘
(3 rows)

Now, the final thing is to replace the 2nd argument to crosstab with a proper
query — a way to fetch all table names prefixed with s0_, which we already
know how to do — and with that the solution is complete:

with
  source_sql as (
    select string_agg(sql_text, E'nUNIONn') || E'nORDER BY load_nr ASC' from (
      select
        'SELECT load_nr, ''' || relname || ''', count(*) FROM ' || relname || ' GROUP BY load_nr' as sql_text
      from
        pg_catalog.pg_class
      where relname like 's0_%'
      order by relname asc
    ) as queries
  )

  -- select * from source_sql;

  select * from public.crosstab(
    (select * from source_sql),
    $$
      select relname from pg_catalog.pg_class
      where relname like 's0_%'
      order by relname asc
    $$
  ) as (
    load_nr int,
    "count s0_table1" int,
    "count s0_table2" int
  );

Coming back to my observation that this will bring you just 99% to your ideal
solution. The reason for that is the fact that crosstab requires a column
list definition, this part, where you have to manually list the table headers
(again):

  ) as (
    load_nr int,
    "count s0_table1" int,
    "count s0_table2" int
  );

Unfortunately, I don’t know of a way to get rid of that last hurdle. And as I
mentioned in the beginning, this query might perform quite badly, but maybe it
is good enough for your use case.