Looping in select query

Question

I want to do something like this:

select id,
    count(*) as total,
    FOR temp IN SELECT DISTINCT somerow FROM mytable ORDER BY somerow LOOP
    sum(case when somerow = temp then 1 else 0 end) temp,
    END LOOP;
from mytable
group by id
order by id

I created working select:

select id,
    count(*) as total,
    sum(case when somerow = 'a' then 1 else 0 end) somerow_a,
    sum(case when somerow = 'b' then 1 else 0 end) somerow_b,
    sum(case when somerow = 'c' then 1 else 0 end) somerow_c,
    sum(case when somerow = 'd' then 1 else 0 end) somerow_d,
    sum(case when somerow = 'e' then 1 else 0 end) somerow_e,
    sum(case when somerow = 'f' then 1 else 0 end) somerow_f,
    sum(case when somerow = 'g' then 1 else 0 end) somerow_g,
    sum(case when somerow = 'h' then 1 else 0 end) somerow_h,
    sum(case when somerow = 'i' then 1 else 0 end) somerow_i,
    sum(case when somerow = 'j' then 1 else 0 end) somerow_j,
    sum(case when somerow = 'k' then 1 else 0 end) somerow_k 
from mytable
group by id
order by id

this works, but it is 'static' - if some new value will be added to 'somerow' I will have to change sql manually to get all the values from somerow column, and that is why I'm wondering if it is possible to do something with for loop.

So what I want to get is this:

id    somerow_a    somerow_b    ....
0     3            2            ....
1     2            10           ....
2     19           3            ....
.     ...          ...
.     ...          ...
.     ...          ...

So what I'd like to do is to count all the rows which has some specific letter in it and group it by id (this id isn't primary key, but it is repeating - for id there are about 80 different values possible).

http://sqlfiddle.com/#!15/18feb/2

Answer 1

SQL is very rigid about the return type. It demands to know what to return beforehand.

For a completely dynamic number of resulting values, you can only use arrays like @Clodoaldo posted. Effectively a static return type, you do not get individual columns for each value.

If you know the number of columns at call time ("semi-dynamic"), you can create a function taking (and returning) polymorphic parameters. Closely related answer with lots of details:

• Dynamic alternative to pivot with CASE and GROUP BY

(You also find a related answer with arrays from @Clodoaldo there.)

Your remaining option is to use two round-trips to the server. The first to determine the the actual query with the actual return type. The second to execute the query based on the first call.

Else, you have to go with a static query. While doing that, I see two nicer options for what you have right now:

1. Simpler expression

select id
     , count(*) AS total
     , count(somecol = 'a' OR NULL) AS somerow_a
     , count(somecol = 'b' OR NULL) AS somerow_b
     , ...
from   mytable
group  by id
order  by id;

How does it work?

• Compute percents from SUM() in the same SELECT sql query

SQL Fiddle.

2. crosstab()

crosstab() is more complex at first, but written in C, optimized for the task and shorter for long lists. You need the additional module tablefunc installed. Read the basics here if you are not familiar:

• PostgreSQL Crosstab Query

SELECT * FROM crosstab(
   $$
   SELECT id
        , count(*) OVER (PARTITION BY id)::int AS total
        , somecol
        , count(*)::int AS ct  -- casting to int, don't think you need bigint?
   FROM   mytable
   GROUP  BY 1,3
   ORDER  BY 1,3
   $$
   ,
   $$SELECT unnest('{a,b,c,d}'::text[])$$
   ) AS f (id int, total int, a int, b int, c int, d int);

Answer 2

Are arrays good for you? (SQL Fiddle)

select
    id,
    sum(totalcol) as total,
    array_agg(somecol) as somecol,
    array_agg(totalcol) as totalcol
from (
    select id, somecol, count(*) as totalcol
    from mytable
    group by id, somecol
) s
group by id
;
 id | total | somecol | totalcol 
----+-------+---------+----------
  1 |     6 | {b,a,c} | {2,1,3}
  2 |     5 | {d,f}   | {2,3}

In 9.2 it is possible to have a set of JSON objects (Fiddle)

select row_to_json(s)
from (
    select
        id,
        sum(totalcol) as total,
        array_agg(somecol) as somecol,
        array_agg(totalcol) as totalcol
    from (
        select id, somecol, count(*) as totalcol
        from mytable
        group by id, somecol
    ) s
    group by id
) s
;
                          row_to_json                          
---------------------------------------------------------------
 {"id":1,"total":6,"somecol":["b","a","c"],"totalcol":[2,1,3]}
 {"id":2,"total":5,"somecol":["d","f"],"totalcol":[2,3]}

In 9.3, with the addition of lateral, a single object (Fiddle)

select to_json(format('{%s}', (string_agg(j, ','))))
from (
    select format('%s:%s', to_json(id), to_json(c)) as j
    from
        (
            select
                id,
                sum(totalcol) as total_sum,
                array_agg(somecol) as somecol_array,
                array_agg(totalcol) as totalcol_array
            from (
                select id, somecol, count(*) as totalcol
                from mytable
                group by id, somecol
            ) s
            group by id
        ) s
        cross join lateral
        (
            select
                total_sum as total,
                somecol_array as somecol,
                totalcol_array as totalcol
        ) c
) s
;
                                                                to_json                                                                
---------------------------------------------------------------------------------------------------------------------------------------
 "{1:{\"total\":6,\"somecol\":[\"b\",\"a\",\"c\"],\"totalcol\":[2,1,3]},2:{\"total\":5,\"somecol\":[\"d\",\"f\"],\"totalcol\":[2,3]}}"

In 9.2 it is also possible to have a single object in a more convoluted way using subqueries in instead of lateral