Total Within A Query

[The Report Guy]

Hi,

I have the following query the gives me the total Balance by pool. I'm
Trying divide the Sum of each pool to the over all balance within a query.

SELECT POOL, Sum(Portfolio.Balance) AS SumOfBalance
FROM Portfolio
GROUP BY Portfolio.POOL

Example:

Pool Balance %
2006-01 10 67%
2006-02 5 33%

Please advice

Thanks

 

[Dale Fye]

One way to approach this:

SELECT Pool, Sum(Balance) as SumOfBalance,
SumOfBalance/DSUM("Balance", "Portfolio") as Pct
FROM Portfolio
GROUP BY PortFolio

Another that would work is the following. I believe this would be faster
with a large dataset, but would have to test it out to see.

SELECT Pool,
Sum(Balance) as SumOfBalance,
Sum(Balance)/SumOfPortfolio as Pct
FROM Portfolio,
(SELECT SUM(Balance) as SumOfPortfolio
FROM tblPortfolio) as PortSum
GROUP BY PortFolio


HTH
Dale

 

[The Report Guy]

Thanks Dale.

It work with DSUM function. I received the error message with the other
statements.