Looping In Select Query

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

Solution 1:

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
    groupby id, somecol
) s
groupby 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)

selectrow_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
        groupby id, somecol
    ) s
    groupby 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 jfrom
        (
            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
                groupby id, somecol
            ) s
            groupby 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

Solution 2:

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'ORNULL) AS somerow_a
     , count(somecol ='b'ORNULL) AS somerow_b
     , ...
from   mytable
groupby id
orderby 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 (PARTITIONBY id)::intAS total
        , somecol
        , count(*)::intAS ct  -- casting to int, don't think you need bigint?FROM   mytable
   GROUPBY1,3ORDERBY1,3
   $$
   ,
   $$SELECTunnest('{a,b,c,d}'::text[])$$
   ) AS f (id int, total int, a int, b int, c int, d int);