How to build this query in postgresql

Question

Supposing I have this data. How to query this in such a way that will result to this in an efficient way. It will sum the qty of all OUT less the IN per item.

Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented Jul 6, 2017 at 5:34
@Renzo, I tried doing it manually like ill sum the OUT first and less the IN but doint it will be inefficient I have a lots of data. select (select sum(out) - select sum(in) from table A). Something like this. — Bigboss
– Bigboss, Commented Jul 6, 2017 at 5:39
@Tim Biegelesen yes. But i just don't know how to build it like that — Bigboss
– Bigboss, Commented Jul 6, 2017 at 5:41

Tim Biegeleisen · Accepted Answer · 2017-07-29 06:18:38Z

3

Try this query:

select
    "Desc",
    sum(case when Type = 'out' then Qty else 0 end) -
    sum(case when Type = 'in'  then Qty else 0 end)
from yourTable
group by "Desc"
    Desc

Note that DESC is a reserved keyword and you should not be naming your databases, tables, or columns using it. I think you would have to escape it in double quotes to get the query to run.

edited Jul 29, 2017 at 6:18

answered Jul 6, 2017 at 5:45

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bigboss Over a year ago

This is exactly what Im looking for. never though CASE WHEN can be used like this.

Nidhi257 · Accepted Answer · 2017-07-06 05:56:17Z

1

select desc, sum(case Type when 'out' then Qty else -Qty end) from test group by desc;

this will be faster.

answered Jul 6, 2017 at 5:56

Nidhi257

9973 gold badges8 silver badges26 bronze badges

Comments

Jonathan Willcock · Accepted Answer · 2017-07-06 05:49:12Z

0

Or

SELECT Description, SUM(Case WHEN rowtype = 'in' then 1 else -1 end * Qty) as rowqty FROM yourtable GROUP BY description

Note that it is not a good idea to use reserved words as column names. DESC and TYPE are asking for trouble!

answered Jul 6, 2017 at 5:49

Jonathan Willcock

5,4303 gold badges25 silver badges36 bronze badges

1 Comment

Bigboss Over a year ago

yes. I just used that for the purpose of this question.

Collectives™ on Stack Overflow

How to build this query in postgresql

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related