Skip site navigation (1) Skip section navigation (2)

Sorting an aggregated column

From: "David Witham" <davidw(at)unidial(dot)com(dot)au>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Sorting an aggregated column
Date: 2004-03-23 03:26:15
Message-ID: CFA248776934FD43847E740E43C346D199DCAA@ozimelb03.ozicom.com (view raw or flat)
Thread:
Lists: pgsql-sql
Hi all,

I ran these commands:

create temp table dw_survey(survey int,question int,answer_bool boolean,answer_int int,answer_char varchar);
insert into dw_survey values(1,1,'t',null,null);
insert into dw_survey values(1,2,'f',null,null);
insert into dw_survey values(1,3,'t',null,null);
insert into dw_survey values(1,4,null,123,null);
insert into dw_survey values(1,5,null,21,null);
insert into dw_survey values(1,6,null,1,null);
insert into dw_survey values(2,1,'t',null,null);
insert into dw_survey values(2,2,'t',null,null);
insert into dw_survey values(2,3,'t',null,null);
insert into dw_survey values(2,4,null,3,null);
insert into dw_survey values(2,5,null,2,null);
insert into dw_survey values(2,6,null,1,null);

and I now have a table with data like this:

         Table "pg_temp_5.dw_survey"
   Column    |       Type        | Modifiers 
-------------+-------------------+-----------
 survey      | integer           | 
 question    | integer           | 
 answer_bool | boolean           | 
 answer_int  | integer           | 
 answer_char | character varying | 

 survey | question | answer_bool | answer_int | answer_char 
--------+----------+-------------+------------+-------------
      1 |        1 | t           |            | 
      1 |        2 | f           |            | 
      1 |        3 | t           |            | 
      1 |        4 |             |        123 | 
      1 |        5 |             |         21 | 
      1 |        6 |             |          1 | 
      2 |        1 | t           |            | 
      2 |        2 | t           |            | 
      2 |        3 | t           |            | 
      2 |        4 |             |          3 | 
      2 |        5 |             |          2 | 
      2 |        6 |             |          1 | 

Answers to a survey can be one of three types - boolean, integer or varchar.
There can be any number of questions in a survey.

I want to summarise the results of the survey like this:

survey | answer1 | answer2 | answer3 | answer4 | answer5 | answer6
-------+---------+---------+---------+---------+---------+--------
1      |    t    |    f    |    t    |   123   |    21   |    1
2      |    t    |    t    |    t    |     3   |     2   |    1

Or even like this:

survey | answers
-------+---------------
1      | t,f,t,123,21,1
2      | t,t,t,3,2,1

In both cases the order of the answers must be ordered by the "question" column.

I can do the second case with a user-defined string concatenating aggregate:

select survey, list (
case when answer_bool = 't' then 'y'::varchar
        when answer_bool = 'f' then 'n'::varchar
        when answer_int is not null then answer_int::varchar
        when answer_char is not null then answer_char::varchar
    end
)
from dw_survey
group by survey
order by survey;

 survey |        list         
--------+---------------------
      1 | y, n, y, 123, 21, 1
      2 | y, y, y, 3, 2, 1

This output is correct in this case but there is no guarantee that the answers will come out in "question" order. I can't see how to incorporate sorting by the "question" column using this approach.

Can anyone suggest either how to improve my current approach or a different approach to get the desired result?

Thanks,

David Witham
Telephony Platforms Architect
Unidial Pty Ltd
Level 1, 174 Peel St North Melbourne,VIC 3051
Australia
Ph: 03 8628 3383
Fax: 03 8628 3399

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-03-23 05:17:24
Subject: Re: Sorting an aggregated column
Previous:From: Gregory S. WilliamsonDate: 2004-03-23 02:16:19
Subject: function definition documentation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group