How To Create Table With Values Summed By Year In Postgres

The postgres database format is: year product sales account 2013 bread 10 001 2013 bread 5 002 2013 peanut 5 001 2013 jam

Solution 1:

first you store the result of the sum sales as t1

select product, year, sum(sales) as sales
  from table
  group by 1,2
  order by 1,2

then you use crosstab()

SELECT *
FROM   crosstab(
  'SELECT product, year, sales
   FROM   t1
   ORDER  BY 1,2')  
AS foo("product" text, "2013" int, "2014" int);

Solution 2:

Select 
Product ,
Sum (case when year =2013 then 1 else 0 end) 2013,
Sum (case when year =2014 then 1 else 0 end) 2014
From your table 
Group by 
Product;

Solution 3:

I see there are several questions about creating crosstab queries with variable amount of columns. It is impossible for regular query but can be done using refcursors.

Lets create function which

1) constructs query like:

select
  name_field,
  sum(value_field) filter (where column_field = c1) as col_1,
  sum(value_field) filter (where column_field = c2) as col_2,
  ...
from
  table
group by
  name_field

where c1, c2, ... are constants for our columns

and 2) returns cursor for this query:

create or replace function ct(
  in p_sql text, -- Data providing query
  in p_name_field varchar, -- Field with titles for rows 
  in p_column_field varchar, -- Field with titles for columns
  in p_value_field varchar, -- Field with numeric data
  p_cursor refcursor) returns refcursor as $$
declare
  q text;
  cols varchar[];
  c varchar;
begin
  -- Collect column names
  execute 'select array_agg(distinct ' || p_column_field || '::varchar) from (' || p_sql || ') t' into cols;

  -- Build query
  q := 'select ' || p_name_field;
  for c in (select unnest(cols)) loop
    q := q || ', sum(' || p_value_field || ') filter (where ' || p_column_field || '::varchar = ' || quote_literal(c::text) || ') as col_' || c;
  end loop;
  q := q || ' from (' || p_sql || ') t group by ' || p_name_field || ' order by ' || p_name_field;

  open p_cursor for execute q;
  return p_cursor;
end; $$ language plpgsql;

Thats it. Now we can use this function for the OP's data like:

begin;
select ct('select * from sales', 'product', 'year', 'sales', 'ct_cur');
fetch all in ct_cur;
-- Yet another usage example:
select ct('select * from sales', 'product, account', 'year', 'sales', 'ct_cur_acc');
fetch all in ct_cur_acc;
-- And another one:
select ct('select * from sales', 'account, year', 'product', 'sales', 'ct_cur_prod');
fetch all in ct_cur_prod;
commit;

And results is:

 product | col_2013 | col_2014 
---------+----------+----------
 bread   |       15 |       18
 jam     |        9 |        8
 peanut  |        5 |        6
(3 rows)

 product | account | col_2013 | col_2014 
---------+---------+----------+----------
 bread   | 001     |       10 |       12
 bread   | 002     |        5 |        6
 jam     | 001     |        4 |        5
 jam     | 002     |        5 |        3
 peanut  | 001     |        5 |        6
(5 rows)

 account | year | col_bread | col_jam | col_peanut 
---------+------+-----------+---------+------------
 001     | 2013 |        10 |       4 |          5
 001     | 2014 |        12 |       5 |          6
 002     | 2013 |         5 |       5 |           
 002     | 2014 |         6 |       3 |           
(4 rows)

Solution 4:

There are several ways you could do this. A crosstab may be the easiest, but it requires the tablefunc extension to be installed, which you may not have superuser privilege for.

Another way is to use subqueries, which makes adding a new year 2 simple copy/paste/update:

WITH 
products as (
  select product from sales group by product
),
sums AS (
   select product, sum(sales) total_sales, year
   from sales
   group by product, year
)

SELECT 
   products.product,
   "2013".total_sales as "2013",
   "2014".total_sales as "2014"
FROM products
LEFT JOIN sums "2013" ON(products.product="2013".product and "2013".year=2013)
LEFT JOIN sums "2014" ON(products.product="2014".product and "2014".year=2014)

ORDER BY product

SQL Fiddle