hello,

I have a table like this:

thing, size, color, type_mov, vary
1, s, red, sell, 1
1, s, red, buy, 2
1, m, green, return, 10
....

and the question is how I can see the total number of products by size
and color having in mind that some type of movement are + and other
are -.

in other words, like stock control.

how I can control this in sql server?
code, procedures?

thanks!

estesiquevale (AT) gmail (DOT) com wrote:

This should get you on the right track:

select thing, size, color, sum(
case when type_mov in ('buy', 'return') then vary else -vary end
) as total_change
from the_table
group by thing, size, color

Ed Murphy wrote:

Also, to avoid having to rewrite this every time you add a new
type of transaction, you may want to create a table like this:

type_mov | effect
---------+---------
buy | incoming
sell | outgoing
return | incoming

and then do:

select t1.thing, t1.size, t1.color, sum(
case when t2.effect = 'incoming' then +vary
when t2.effect = 'outgoing' then -vary end
) as total_change
from the_table t1
join the_new_table t2 on t1.type_mov = t2.type_mov
group by t1.thing, t1.size, t1.color