CODEWITHSUNDEEP
postgresql
Bigboss
Bigboss

Reputation: 365

How to build this query in postgresql

enter image description here

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.

enter image description here

Upvotes: 0

Views: 48

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

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.

Upvotes: 3

Nidhi257
Nidhi257

Reputation: 967

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

this will be faster.

Upvotes: 1

Jonathan Willcock
Jonathan Willcock

Reputation: 5235

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!

Upvotes: 0

Related Questions