>Hi everybody,
>
>Using VFP8 do you think it's possible to do something like
>
>select Account, sum(balance) as Total_Balance, sum(iif(MyCond,value,0)) as MyCond_Balance from ... group by 1 having MyCond_Balance/Total_Balance > 0.1 (10 %)
>
>I also need to include percent into result.
>
>Or do you think I should better do this in 2 select statements to get all balances first and then filter based on 10% condition?
>
>Thanks in advance.
This works for me (VFP8 SP1)
CREATE CURSOR crsTest (Account I, balance N(14,2), Value1 N(14,2))
RAND(-1)
FOR asd = 1 TO 50
INSERT INTO crsTest VALUES (asd%5+1, RAND()*1000, RAND()*100000)
NEXT
BROW
myCond = .t.
select Account, sum(balance) as Total_Balance,;
sum(iif(MyCond,value1,0)) as MyCond_Balance,;
sum(iif(MyCond,value1,0))/ sum(balance) AS TestMe;
from crsTest;
group by 1;
having MyCond_Balance/Total_Balance > 0.1
But I always been suspicious with HAVING clause. Do test what is faster two SELECTs or one with HAVING
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.