Re: apply sum function after group by extract month date

Hey there,

Is this what you are after? Note that I've changed the 'money' type to numeric.

create table testgroupsum (itemname varchar(50),date date,amount numeric);
set datestyle to DMY;
insert into testgroupsum (itemname,date,amount) values ('item
1','2013-01-01','100');
insert into testgroupsum (itemname,date,amount) values ('item
2','2013-01-10','35');
insert into testgroupsum (itemname,date,amount) values ('item
3','2013-02-20','50');
insert into testgroupsum (itemname,date,amount) values ('item
a','2013-01-25','-500');
insert into testgroupsum (itemname,date,amount) values ('item
b','2013-02-28','-20');

SELECT * FROM testgroupsum;

SELECT EXTRACT(month FROM(date)), SUM(amount) FROM testgroupsum GROUP
BY EXTRACT(month FROM(date));

-- Then if you want to put an dollar or whatever you could do this:

SELECT EXTRACT(month FROM(date)), '£' || SUM(amount) FROM testgroupsum
GROUP BY EXTRACT(month FROM(date));

DROP TABLE testgroupsum;

Regards

James

On 10 September 2013 10:02, e-letter <inpost(at)gmail(dot)com> wrote:
> Readers
>
> How to apply a sum function to rows grouped by date?
>
> create table testgroupsum (itemname varchar(50),date date,amount money);
> set datestyle to DMY;
> insert into testgroupsum (itemname,date,amount) values ('item
> 1','2013-01-01','100');
> insert into testgroupsum (itemname,date,amount) values ('item
> 2','2013-01-10','35');
> insert into testgroupsum (itemname,date,amount) values ('item
> 3','2013-02-20','50');
> insert into testgroupsum (itemname,date,amount) values ('item
> a','2013-01-25','-500');
> insert into testgroupsum (itemname,date,amount) values ('item
> b','2013-02-28','-20');
> drop table testgroupsum;
>
> How to change currency unit for the data type money, i.e. to change
> the currency sign from dollar ($) shown in a query result?
>
> The manual states that date data can be extracted, so the following was tried:
>
> select extract (month from (select date from testgroupsum where date >
> '2013-01-01'));
> ERROR: more than one row returned by a subquery used as an expression
>
> The objective is to be able to apply sum function to items 1 and 2,
> grouped by month value = 1. Then to apply a subsequent _net_ sum 1
> items 1 and 2, item a, i.e. for month value = 1, compute sum
> 100,35,-500; month value = 2, compute sum 50,-20; etc.
>
> Thanks in advance.
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice