How to make a query using repeated WHERE clauses and LEFT JOINS more dynamic (without crostab)

Question

EDIT: I am on an older version of Postgresql that does not support crostab.

I have a query I am trying to improve by making it easier to add new value a table that is meant to roll up information for a single user

I basically have to write a new left join and where clause each time I add a value to this column below called algorithms:

┌───────────────────────────────┐
│          algo                 │
├───────────────────────────────┤
│ Algorithm1                    │
│ Algorithm2                    │
│ Algorithm3                    │
└───────────────────────────────┘

Here is the query I wrote to genearte the output:

select a.userid, a.algo, a.algo1_cnt, b.algo, b.algo2t_cnt, c.algo, c.algo3_cnt
    from
    (select userid, algo, count(*) as algo1_cnt
    from test_table
    where (algo = 'Algorithm1')
    group by 1,2 ) as a
    LEFT OUTER JOIN
    ( select userid, algo, count(*) as algo2_cnt
    from test_table
    where (algo = 'Algorithm2')
    group by 1,2 ) as b
    ON
    (a.userid = b.userid)
    LEFT OUTER JOIN
    ( select userid, algo, count(*) as algo3_cnt
    from test_table
    where (algo = 'Algorithm3')
    group by 1,2 ) as c
    ON
    (a.userid = c.userid)

The output of the query now looks like:

┌──────────────────────┬────────────────┬───────────┬───────┬───────────┬───────────────────────────────┬───────────┐
│        userid        │     algo1      │ algo1_cnt │ algo2 │ algo2_cnt │             algo3             │ algo3_cnt │
├──────────────────────┼────────────────┼───────────┼───────┼───────────┼───────────────────────────────┼───────────┤
│ user1                │ algo1          │         3 │       │           │ algo3                         │         2 │
│ user2                │ algo1          │         2 │       │           │                               │           │

Question: What is the best way to modify the query to be able to read the distinct values from the algo column in a dynamic fashion and generate the same outpuy?

What I mean if I add a new value called Algorithm4 to the algo column can I levarage PL/PGSQL or some other dyanmic recurision to generate the same output without having to use WHERE (algo = 'Algorithm4')?

Possible duplicate of http://stackoverflow.com/q/3002499/673826 and [the answer](http://stackoverflow.com/a/11751905/673826) — mlt, Jun 20 '14 at 20:28
It is not a duplicate. Same or similar problem is not duplicate. Indeed this problem is quite different. If someone thinks that whenever a question involves crosstab it is a duplicate then he is in the wrong site. Questions, even the exact same problem, get explained in very different ways and it is that variety that makes better questions and makes some get more votes than others. — Clodoaldo Neto, Jun 20 '14 at 21:35

Clodoaldo Neto · Answer 1 · 2014-06-20T22:17:48.967

You can use crosstab but if you can split arrays somewhere else then this is much simpler

select 
    user_id, 
    array_agg(algo) as algo, 
    array_agg(algo_count) as algo_count
from (
    select userid, algo, count(*) as algo_count
    from test_table
    group by 1, 2
) s
group by 1

JSON aficionados can also have it

select
    user_id,
    format(
        '{%s}',
        string_agg(format('"%s": %s', algo, algo_count), ', ')
    )::json as algo_obj
from (
    select userid, algo, count(*) as algo_count
    from test_table
    group by 1, 2
) s
group by 1

How to make a query using repeated WHERE clauses and LEFT JOINS more dynamic (without crostab)

1 Answers1