--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@...> wrote:

>
> kokok_kokok wrote:
> >
> > I need to do:
> >
> > select CustomerId, Sales, (select sum(Sales) from Customers) as Total
> > from Customers
> >
> > It works fine, but for each row, Firebird executes the subquery
> > "select sum(Sales) from Customers".
> >
>
> Are you sure? At one time, I'm quite sure that the compiler/optimizer
> were good enough to remove loop invariants.
>

It possibly has been improved since, but 1.5.3 does not appear to
optimise it:

PLAN (CUSTOMERS NATURAL)
PLAN (CUSTOMERS NATURAL)

A quick experiment with my tables shows that there are

n * (n + 1) reads

where n is the number of records in CUSTOMERS

kokok_kokok,

The easiest way to handle this in a single statement is to create a
selectable stored procedure.

CREATE OR ALTER PROCEDURE MYPROC
RETURNS
(
CUSTOMERID BIGINT
SALES NUMERIC(15,4),
TOTAL NUMERIC(15,4)
)
AS
BEGIN
select COALESCE(sum(Sales), 0)
from Customers
INTO :TOTAL;

FOR select CustomerId, Sales
from Customers
INTO :CUSTOMERID, :SALES
DO
BEGIN
SUSPEND;
END
END
^

Then you can run

SELECT CUSTOMERID, SALES, TOTAL
FROM MYPROC;

This should return the results with 2*n reads for customer, which is
more efficient than the first query as soon as you have more than 2
records in your Customers table.

Adam