Aggregate several values from a field, depending on a code from another field

Question

I need to calculate several values from a field in a table depending on a code in another field of the same table.

The table is in a PostgreSQL 8.3 database.

Table:

cod_1 | cod_2 |    date    |  cod_sum| import
1     |   2   | 01/01/2012 |     a   | 1.1
1     |   2   | 01/01/2012 |     a   | 1.2
1     |   2   | 01/01/2012 |     b   | 1.1
1     |   2   | 01/01/2012 |     b   | 1.1
1     |   2   | 01/01/2012 |     c   | 1.1
1     |   2   | 01/01/2012 |     c   | 1.1

I need something like this:

cod_1 | cod_2 |date      |sum_import_a|sum_import_b|calc_import_abc(a+b-c)
1     |    2  |01/01/2012|    2.3     |   2.2      |   2.3 (2.3+2.2-2.2)

I hope you can lend a hand ...

Answer 1

Supposing your column set is always the same (regarding values a, b and c), here's what you need:

select
    cod_1, cod_2, date, sum_import_a, sum_import_b, 
    sum_import_a + sum_import_b - sum_import_c as calc_import_abc
from
(    
    select cod_1, cod_2, date
       sum(case cod_sum when 'a' then 1 else 0 end) sum_import_a,
       sum(case cod_sum when 'b' then 1 else 0 end) sum_import_b,
       sum(case cod_sum when 'c' then 1 else 0 end) sum_import_c
    from YourTable
    group by cod_1, cod_2, date
) sel

Answer 2

With the new window functions (PostgreSQL 8.4 or later) you could even do that without subquery:

SELECT cod_1, cod_2, date
      , first_value(sum(import))  OVER w AS sum_import_a
      , nth_value(sum(import), 2) OVER w AS sum_import_b
      ,   first_value(sum(import))  OVER w
        + nth_value(sum(import), 2) OVER w
        - last_value(sum(import))   OVER w AS calc_import_abc
FROM   tbl
GROUP  BY 1, 2, 3, cod_sum
WINDOW w AS (ORDER BY cod_sum
             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
LIMIT  1;

For simplicity, however, (and for PostgreSQL 8.3) I would do it like @Adrian demonstrates, just without the logic error:

SELECT cod_1, cod_2, date, sum_import_a, sum_import_b
     ,   sum_import_a
       + sum_import_b
       - sum_import_c AS calc_import_abc
FROM  (
    SELECT cod_1, cod_2, date
          , sum(CASE WHEN cod_sum = 'a' THEN import ELSE 0 END) sum_import_a
          , sum(CASE WHEN cod_sum = 'b' THEN import ELSE 0 END) sum_import_b
          , sum(CASE WHEN cod_sum = 'c' THEN import ELSE 0 END) sum_import_c
    FROM   tbl
    GROUP  BY 1, 2, 3
    ) x;