Consider the table T which when tabulated, looks like this:
select sum Qty by Flag1,Flag2 from T
Flag1 Flag2 Qty
`Sunny `Hot 20
`Sunny `Cold 40
`Rainy `Hot 60
`Rainy `Cold 80
1) Is there a nifty way of computing the overall fraction of aggregate Qty that falls in each bucket (0.1,0.2,0.3,0.4)? Obviously one could do something like
select Qty % sum Qty from select sum Qty by Flag1,Flag2 from T
but is there anyway of doing this in one swoop (i.e. with only one select/exec statment),as this could get kind of cumbersome if you're doing this for many variables?
2) Now imagine I want to create a column that has the relative fraction of Qty but marginalized by Flag1. How do I create the following table?
Flag1 Flag2 Qty FracByFlag1
`Sunny `Hot 20 0.333
`Sunny `Cold 40 0.667
`Rainy `Hot 60 0.429
`Rainy `Cold 80 0.571
1) I think it is simplest to sum the quantity of the column separately during division:
update (sum Qty) % sum T[`Qty] by Flag1, Flag2 from T
2) fby is exactly what you are after:
update FracByFlag1: Qty % (sum;Qty) fby Flag1 from T