Getting net change from same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting net change from same table

Author

Topic

allanurban
Starting Member

21 Posts

Posted - 2013-06-07 : 06:21:55

Hello all

I have a table which contains 'productId', 'way' and 'quanitity'
way indicates if I have bought ('B') or sold ('S') the product and quantity is only positive integers. How can I get the net change from this table?

I can get the seperate results and could just do it manually, but would really like this in a single query.

SELECT productId, way, SUM(quantity)
FROM table
WHERE way = 'B'
GROUP BY productId, way

Anyone who can push me in the right direction?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-07 : 06:36:37

something like this

select productid, sum(case when way = 'B' then 0 - quantity else quantity end) as Qty
from table
group by productid

Duane.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 06:44:27

[code]
SELECT productId,[B] AS Bought,[S] AS Sold,[B]-[S] AS Net
FROM (SELECT productId, way, quantity AS Qty
FROM table
)t
PIVOT(SUM(Qty) FOR way in ([B],[S]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-07 : 06:44:28

quote:
Originally posted by ditch

something like this

select productid, sum(case when way = 'S' then 0 - quantity else quantity end) as Qty
from table
group by productid

Duane.

Cheers
MIK

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-07 : 06:48:44

Hi Mik
That depends if you are talking about Stock Holding Or sales..... I don't believe it was specified.

Duane.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-07 : 06:58:33

Absolutely agree, it was not specified.

Perhaps I should have given a narrative too. Actually I was going to suggest same query as yours but you posted before me :) and that instead of reposting it I just took yours and repalce 'B' with 'S' Since that is how I perceived it.

Cheers
MIK

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-07 : 07:03:11

quote:
Originally posted by visakh16

SELECT productId,[B] AS Bought,[S] AS Sold,[B]-[S] AS Net
FROM (SELECT productId, way, SUM(quantity) AS Qty
FROM table
)t
PIVOT(SUM(Qty) FOR way in ([B],[S]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Visakh, seems you missed to add group by :)

SELECT id,[B] AS Bought,[S] AS Sold
FROM (SELECT id, way, sum(quantity) AS Qty
FROM @tab
group by id,way
)t
PIVOT(SUM(Qty) FOR way in ([B],[S]))p

Cheers
MIK

allanurban
Starting Member

21 Posts

Posted - 2013-06-07 : 07:10:30

Thank you for the quick help everyone.

Much appriciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 07:31:06

quote:
Originally posted by MIK_2008

quote:
Originally posted by visakh16

SELECT productId,[B] AS Bought,[S] AS Sold,[B]-[S] AS Net
FROM (SELECT productId, way, SUM(quantity) AS Qty
FROM table
)t
PIVOT(SUM(Qty) FOR way in ([B],[S]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Visakh, seems you missed to add group by :)

SELECT id,[B] AS Bought,[S] AS Sold
FROM (SELECT id, way, sum(quantity) AS Qty
FROM @tab
group by id,way
)t
PIVOT(SUM(Qty) FOR way in ([B],[S]))p

Cheers
MIK

I think you didnt refresh

I edited it before you posted this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-07 : 08:04:16

Nice solution Vikash.

Duane.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 08:12:54

quote:
Originally posted by ditch

Nice solution Vikash.

Duane.

thanks

But for the given scenario I think your approach is better.
There's no need for OP to crosstab if the intention is just to get the net value.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources