Have aggregate function group results in one row

Question

I have a table that has the following columns:

debit
credit
payment_method
class

Now I will perform this query:

 select sum(debit - credit), payment_method, class 
 from my_table 
 group by payment_method, class

And since I have various payment methods and various classes, I will have several rows of the result set.

Now I want to be able to aggregate this result and have the output grouped by class only, for example for a certain class 001 I want to have: payment_method1, sumofpayment_method1, payment_method2, sumofpayment_method2, payment_method3, sumofpayment_method3 in one row.

Is it possible in Postgres 9.1?

Update:

The table definition is as follows:-

debit numeric(10,2)
credit numeric(10,2)
payment_method varchar(20)
class varchar(25)

Will you have exactly three pairs (payment method and sum) for every class? — András Váczi, Jul 23 '13 at 10:15
You should provide a full table definition with column data types. — Erwin Brandstetter, Jul 23 '13 at 10:33
@dezso not really they could be more than three. I used three for illustration purposes only — G. Ngotho, Jul 23 '13 at 10:33

score 5 · Accepted Answer · edited May 23 '17 at 12:40

5

You are looking for the crosstab() function provided by the additional module tablefunc.

Then your function could look like this:

SELECT * FROM crosstab(
   'SELECT class, payment_method, sum(debit - credit) AS saldo
    FROM   tbl
    GROUP  BY 1,2
    ORDER  BY 1,2'

   ,$$VALUES 
        ('payment_method1'::text), ('sumofpayment_method1')
      , ('payment_method2'), ('sumofpayment_method2')
      , ('payment_method3'), ('sumofpayment_method3')$$
   )
AS ct (class text
     , payment_method1 numeric, sumofpayment_method1 numeric
     , payment_method2 numeric, sumofpayment_method2 numeric
     , payment_method3 numeric, sumofpayment_method3 numeric);

I am using all 6 "types" you mentioned. Well, I see that only half of them are actually intended as type. Either way, substitute your actual types.
More details, links and explanations in this related answer on SO.

edited May 23 '17 at 12:40

Community

1

answered Jul 23 '13 at 10:38

Erwin Brandstetter

175,982
27
439
600

u have an idea why I get this error:- function crosstab(unknown, unknown) does not exist No function matches the given name and argument types. You might need to add explicit type casts. I have install tablefunc module – G. Ngotho Jul 23 '13 at 11:04
@G.Ngotho: Did you install the additional module tablefunc as instructed in the linked answer? And do you have the schema (usually public) in your search_path? (SHOW search_path;) – Erwin Brandstetter Jul 23 '13 at 11:07
Well I ran CREATE EXTENSION tablefunc and I got error ERROR: type "tablefunc_crosstab_2" already exists And yes I have the schema public in my search path – G. Ngotho Jul 23 '13 at 11:12
@G.Ngotho: Do you have half an installation carried over from an older version of Postgres maybe? Then you'd have to sanitize your installation: Delete all existing tablefunc objects until you can run a complete new installation. Try DROP EXTENSION first. You can also check the list of functions in your public schema to begin with. – Erwin Brandstetter Jul 23 '13 at 11:15

Have aggregate function group results in one row

1 Answers1

Linked