|
|
Posted by Ed Murphy on 05/07/07 17:31
Ed Murphy wrote:
> estesiquevale@gmail.com wrote:
>
>> 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 -.
>
> 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
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
Navigation:
[Reply to this message]
|