Lists: | pgsql-general |
---|
From: | Jashaswee <sweet(dot)rinky72(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | postgresql query |
Date: | 2013-06-19 10:51:43 |
Message-ID: | 1371639103750-5759846.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I have numeric values in a numeric column.the column has two parts.i want to
split in 2 differnet column .
The column value looks like this:
Quantity
2000
-1000
both the quantity values are of a same product.but i want these in a single
line.
so what i want is a result set that looks like:
In quantity Out quantity
----------- ------------
2000 -1000
how can i get this in a select statement ?
--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From: | AI Rumman <rummandba(at)gmail(dot)com> |
---|---|
To: | Jashaswee <sweet(dot)rinky72(at)gmail(dot)com> |
Cc: | pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: postgresql query |
Date: | 2013-06-19 18:43:54 |
Message-ID: | CAGoODpes++CVid7H6ub5M+vu0TKdJQQfbokiew5G8hCXwG35iA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Which version of Postgresql are you using?
However, you may use string_agg like below if its available in your version:
\d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
amt | integer |
select * from t1;
i | amt
---+-----
1 | 20
1 | 30
2 | 30
(3 rows)
select i, string_agg(amt::text,',') as c from t1 group by i;;
i | c
---+-------
1 | 20,30
2 | 30
(2 rows)
Have a good day.
On Wed, Jun 19, 2013 at 6:51 AM, Jashaswee <sweet(dot)rinky72(at)gmail(dot)com> wrote:
> I have numeric values in a numeric column.the column has two parts.i want
> to
> split in 2 differnet column .
> The column value looks like this:
>
> Quantity
> 2000
> -1000
>
> both the quantity values are of a same product.but i want these in a
> single
> line.
> so what i want is a result set that looks like:
> In quantity Out quantity
> ----------- ------------
> 2000 -1000
>
> how can i get this in a select statement ?
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From: | Victor Yegorov <vyegorov(at)gmail(dot)com> |
---|---|
To: | Jashaswee <sweet(dot)rinky72(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: postgresql query |
Date: | 2013-06-19 18:56:32 |
Message-ID: | CAGnEboi-pB+hQM8MYNS0u+HVFgCeL7JnaWRfz=GeZuRPVVETOQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2013/6/19 Jashaswee <sweet(dot)rinky72(at)gmail(dot)com>
> The column value looks like this:
>
> Quantity
> 2000
> -1000
>
> both the quantity values are of a same product.but i want these in a
> single
> line.
> so what i want is a result set that looks like:
> In quantity Out quantity
> ----------- ------------
> 2000 -1000
>
How do we decide wether 2000 or -1000 refers to the “In quantity”?
Can you show a complete table's structure, please?
--
Victor Y. Yegorov
From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: postgresql query |
Date: | 2013-06-19 23:46:10 |
Message-ID: | 1371685570732-5760003.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Jashaswee wrote
> I have numeric values in a numeric column.the column has two parts.i want
> to split in 2 differnet column .
> The column value looks like this:
>
> Quantity
> 2000
> -1000
>
> both the quantity values are of a same product.but i want these in a
> single line.
> so what i want is a result set that looks like:
> In quantity Out quantity
> ----------- ------------
> 2000 -1000
>
> how can i get this in a select statement ?
I presume this is a debit/credit situation.
Basically you use a CASE expression to put the amount into the correct
column depending on whether it is greater or less than zero. The rest of
the query is simply a matter of what kind of detail you want.
WITH make_debit_credit_columns_for_each_record AS (
SELECT ...
, CASE WHEN amt >= 0 THEN amt ELSE 0.00 END AS debit
, CASE WHEN amt < 0 THEN amt ELSE 0.00 END AS credit
FROM source_table
)
SELECT ..., SUM(debit) AS total_debit, SUM(credit) AS total_credit
FROM make_debit_credit_columns_for_each_record
GROUP BY ...
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846p5760003.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From: | Jashaswee <sweet(dot)rinky72(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: postgresql query |
Date: | 2013-06-24 05:35:01 |
Message-ID: | 1372052101163-5760589.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
i am using postgresql 8.4 version..but this doesn't support the function
--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846p5760589.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From: | Jashaswee <sweet(dot)rinky72(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: postgresql query |
Date: | 2013-06-24 06:22:10 |
Message-ID: | 1372054930103-5760593.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
i have tried in that way but its showing that the debit column doesn't exist
--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846p5760593.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: postgresql query |
Date: | 2013-06-24 14:44:00 |
Message-ID: | 1372085039636-5760666.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Jashaswee wrote
> i have tried in that way but its showing that the debit column doesn't
> exist
So show us what exactly it is that you tried and maybe someone can tell you
what is wrong.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846p5760666.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.