pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSUM Array values query
2+ messages / 2 participants
[nested] [flat]
* SUM Array values query
@ 2009-05-18 10:49 Dani Castaños <dcastanos@androme.es>
0 siblings, 1 reply; 2+ messages in thread
From: Dani Castaños @ 2009-05-18 10:49 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I've this query including arrays:
SELECT hour[1:5], statistics_date
FROM statistics_daily
WHERE statistics_date = to_date( '03/01/2008', 'DD/MM/YYYY' )
Result:
hour | statistics_date
----------------------------+-----------------
{1800,1800,1800,1800,1800} | 2008-01-03
I'm just wondering if there's some way to retrieve the hour column as the
sum of the array values... Just like this:
hour | statistics_date
----------------------------+-----------------
9000 | 2008-01-03
Hour is a bigint[] array column.
My version of postreSQL is 8.1.9
Thank you in advanced!
P.S.: Sorry if I had send it before, but I think I was not subscribed to the
mailist.
--
Dani Castaños Sánchez
dcastanos@androme.es
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SUM Array values query
@ 2009-05-18 13:31 Pawel Socha <pawel.socha@gmail.com>
parent: Dani Castaños <dcastanos@androme.es>
0 siblings, 0 replies; 2+ messages in thread
From: Pawel Socha @ 2009-05-18 13:31 UTC (permalink / raw)
To: Dani Castaños <dcastanos@androme.es>; +Cc: pgsql-sql
2009/5/18 Dani Castaños <dcastanos@androme.es>:
> Hi all,
>
> I've this query including arrays:
>
> SELECT hour[1:5], statistics_date
> FROM statistics_daily
> WHERE statistics_date = to_date( '03/01/2008', 'DD/MM/YYYY' )
>
> Result:
>
> hour | statistics_date
> ----------------------------+-----------------
> {1800,1800,1800,1800,1800} | 2008-01-03
>
>
> I'm just wondering if there's some way to retrieve the hour column as the
> sum of the array values... Just like this:
>
> hour | statistics_date
> ----------------------------+-----------------
> 9000 | 2008-01-03
>
>
> Hour is a bigint[] array column.
> My version of postreSQL is 8.1.9
>
> Thank you in advanced!
>
> P.S.: Sorry if I had send it before, but I think I was not subscribed to the
> mailist.
>
> --
> Dani Castaños Sánchez
> dcastanos@androme.es
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Create function in pure sql and ...
,-[15:27:18]merlin@[local]:5432=
`-merlin>create function array_sum (bigint[]) returns bigint as $$
SELECT sum($1[i])::bigint FROM
generate_series(array_lower($1,1),array_upper($1,1)
) index(i); $$ language sql;
CREATE FUNCTION
Time: 16,203 ms
,-[15:28:02]merlin@[local]:5432=
`-merlin>select array_sum(col_array) from t30;
array_sum
-----------
9000
(1 row)
:)
--
Pawel Socha
pawel.socha@gmail.com
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-05-18 13:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-05-18 10:49 SUM Array values query Dani Castaños <dcastanos@androme.es>
2009-05-18 13:31 ` Pawel Socha <pawel.socha@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox