Postgres Pro
Downloads
Home   >   mailing lists

Thread: apply sum function after group by extract month date

apply sum function after group by extract month date

From
e-letter
Date:
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.

Re: apply sum function after group by extract month date

From
James David Smith
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@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

Re: apply sum function after group by extract month date

From
e-letter
Date:
On 10/09/2013, James David Smith <james.david.smith@gmail.com> wrote:
> Hey there,
>
> Is this what you are after? Note that I've changed the 'money' type to
> numeric.
>

Why did you change the data type? Answered my own question by changing
the data type to numeric as you did:

 date_part | ?column?
-----------+----------
         1 | £-730
         2 | £60
(2 rows)

The position of the negative sign (-) is wrong; presumably this a
consequence of the 'numeric' data type that cannot be changed?

The extract function:
>
> SELECT EXTRACT(month FROM(date)), SUM(amount) FROM testgroupsum GROUP
> BY EXTRACT(month FROM(date));
>

returns:

 date_part |   sum
-----------+----------
         1 | -$365.00
         2 |   $30.00
(2 rows)

> -- 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));
>

returns:

 date_part |  ?column?
-----------+------------
         1 | £-$365.00
         2 | £$30.00
(2 rows)

Re: apply sum function after group by extract month date

From
Kevin Grittner
Date:
e-letter <inpost@gmail.com> wrote:

I didn't understand the first part of your question.  Be sure you
are clear about selecting a set of rows based on date and grouping
(summarizing) rows based on date (or some part of date?).

> How to change currency unit for the data type money, i.e. to
> change the currency sign from dollar ($) shown in a query result?

How do you have the locale information set?  In particular,
lc_monetary?

test=# show lc_monetary ;
 lc_monetary
-------------
 en_US.UTF-8
(1 row)

test=# select '123.45'::money;
  money
---------
 $123.45
(1 row)

test=# set lc_monetary = 'en_GB.UTF-8';
SET
test=# select '123.45'::money;
  money
---------
 £123.45
(1 row)

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company