Re: postgresql query

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.