Thread: postgresql query
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.
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.rinky72@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2013/6/19 Jashaswee <sweet.rinky72@gmail.com>
How do we decide wether 2000 or -1000 refers to the “In quantity”?
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
Victor Y. Yegorov
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.
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.
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.
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.