🚀 go-pugleaf

Hi.

Try:

for
  select field_a, sum(field_b)
  from t
  group by field_a
  into :v_a, :v_sum_b
do begin
  if (v_a) = 1 then some_var_1 = v_sum_b;
  else if (v_a) = 2 then some_var_2 = v_sum_b;
  /* and more if's if ther are more distinct values in field_a */
end


Maxim.


"Isaac" <no@no.com> ???????/???????? ? ???????? ?????????:
news:483bca8e@newsgroups.borland.com...
> Hello,
> I need to to sum a field and store the result into a variable in a
> stored procedure, can I use one select statement to get the sum based on
> a condition from another field
>
> ex
> sum(field_a) when field_b = 1
> sum(field_a) when field_b = 0
>
> field_a        field_b
> 5                 1
> 10                0
> 52                1
>
>
> or I have to use two select ? sorry this may appear stupid but I am
> asking to improve performance
>
> Thanks

Maxim Shiryaev wrote:
> Hi.
> 
> Try:
> 
> for
>   select field_a, sum(field_b)
>   from t
>   group by field_a
>   into :v_a, :v_sum_b
> do begin
>   if (v_a) = 1 then some_var_1 = v_sum_b;
>   else if (v_a) = 2 then some_var_2 = v_sum_b;
>   /* and more if's if ther are more distinct values in field_a */
> end
Is this faster than using Two Selects

Select sum(f2) from table_1 where f1 = 1
Select sum(f2) from table_2 where f1 = 0

Thanks

Please Mr Maxim Shiryaev confirm if this method is faster than 2 Selects

Thanks

"Isaac" <no@no.com> wrote in message news:483c6256@newsgroups.borland.com...
> Please Mr Maxim Shiryaev confirm if this method is faster than 2 Selects


Should be pretty quick to test. Try it.

Often there is not way to say positively which is faster, it depends on your 
tables, anount of data, indexes, etc.

-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca) 

Wayne Niddery (TeamB) wrote:
> Should be pretty quick to test. Try it.
> 
> Often there is not way to say positively which is faster, it depends on 
> your tables, anount of data, indexes, etc.
> 
I dont guarantee that my test will accurate to determine however I will 
use for select

Thanks

I cannot confirm that it will be faster in all cases.

But _generally_, "sequential scan and in-memory sorting is faster than
several index scans". (C) Someone from FB dev team.
And I can confirm that in most cases it's so.
Especially if your indicies are not very selective.

So it all depends.

Probably the ultimate way would be:

   declare variable v1 integer;
   declare variable v2 integer;

   v1 = 0;
   v2 = 0;

   select field_a, field_b
   from t
   into :v_a, :v_b
 do begin
   if (v_a = 1) then v1 = v1 + v_b;
   else if (v_a = 2) then v2 = v2 + v_b;
   /* and more if's if ther are more distinct values in field_a */
 end

But here you again trade code clarity and "declarativeness" for performance.

Just test it.

But if there are many different values in field_a and you count only sums
for subset of them than several indexed scans would be faster of course.


Maxim.


"Isaac" <no@no.com> ???????/???????? ? ???????? ?????????:
news:483c12b2@newsgroups.borland.com...
> Maxim Shiryaev wrote:
> > Hi.
> >
> > Try:
> >
> > for
> >   select field_a, sum(field_b)
> >   from t
> >   group by field_a
> >   into :v_a, :v_sum_b
> > do begin
> >   if (v_a) = 1 then some_var_1 = v_sum_b;
> >   else if (v_a) = 2 then some_var_2 = v_sum_b;
> >   /* and more if's if ther are more distinct values in field_a */
> > end
> Is this faster than using Two Selects
>
> Select sum(f2) from table_1 where f1 = 1
> Select sum(f2) from table_2 where f1 = 0
>
> Thanks

Hello,
In my case I have only two possibilities so I will use for select even 
if it is not clear

Thanks alot Maxim I learned something new