Calculate Total Column

I need to calculate the Average by Location and then sum the Average for a Total that is displayed on a report. Following is my stored procedure so far:

SELECT tblLocn.LocnCode, tblLocn.LocnDesc,
#work.StartDate, #work.ZValue,
J1.ZAvg ............. Average by locn -- want the sum of all locns here
FROM tblLocn
LEFT JOIN #work ON tblLocn.LocnCode = #work.LocnCode
LEFT JOIN (SELECT #work.LocnCode,
ZAvg = Avg(#work.ZValue)
FROM #work
GROUP BY #work.LocnCode) J1 ON tblLocn.LocnCode = J1.LocnCode

How would I change this procedure to display either a) total average on each row, or b) location average on 1 row only so when I Sum this field it is the correct value.
I am aware that I could create a workfile with the Total value and then Join to that. Is there another alternative? ThanksJ1.ZAvg is your average by location, so when you sum it on a report you will get the sum of all averages.

Bigger question: are you sure this the operation you want? Your statistics will be skewed if your location don't have equal number of records. Consider:
Sum(Avg(1,2,3,4,5), Avg(6,7,8)) = 10

But the same values distributed across different locations yields this:
Sum(Avg(1,2,3), Avg(4,5,6,7,8)) = 8

If you just want the average per location, this value is constant across all locations since it applies to the entire enterprise, and can be calculated separately:
Sum(#work.ZValue)/Count(Locations)

blindman|||I do want the average by location as I have it coded. The problem with just summing the J1.ZAvg field is that the SELECT statement is not grouped by location but rather by DATE. This means that the J1.ZAvg field appears on each row for the location. If I sum that up, I would get an exploded value. Make sense?|||If I change the J1.ZAvg to Sum(J1.ZAvg), that would give me the total I want, except that it seems I cannot use the Sum unless I use a GROUP BY clause. In Access I used the DSUM aggregate function which is not available for Access Project. I may be missing something as I'm fairly new to SQL Server.|||So calculate the value once, store it in a variable, and include it in your output select statement:

set @.SumLocationAvgs = sum((select avg(zwork) from #Workd group by location))

Note: the above is just pseudo-code. You'll have to write for your specification.

blindman|||Makes total sense! Thanks a bunch.|||Oops .. I get the following message:

ERROR 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Set @.SumLocationAvgs = Sum((SELECT Avg(#work.ZValue) FROM #work GROUP BY LocnCode))|||Try this instead:

Set @.SumLocationAvgs =
(select Sum(SubQuery.LocnCodeAvg)
from (SELECT Avg(#work.ZValue) LocnCodeAvg FROM #work GROUP BY LocnCode) SubQuery)|||Exactly what I needed. Thank you.

business logic location

For quite a while I've had pretty good success with putting most of my
business logic in stored procedures and triggers. It's fast, relational,
searchable, and updatable without any recompiling.
At the moment I'm considering supporting both SQL Server and Oracle, and it
seems to me that to support multiple database platforms the business logic
needs to be moved out of the database layer. Not only would this seem
necessary to me, but it would also seem to be ugly, much more complicated,
harder to maintain, and slower, maybe far slower.
This is the first time I've looked at this problem; it was my assessment
years ago that deep business logic (not the minor stuff associated with just
the UI) in hard code was a bad idea. I was hoping someone could share their
point of view on this area with me, maybe point out some examples of how
multiple database support is currently done, and how feasable (automatable)
it is to port data, with stored procedures and triggers, between SQL and
Oracle.
PaulOn Mon, 19 Sep 2005 10:44:04 -0400, PJ6 wrote:

>For quite a while I've had pretty good success with putting most of my
>business logic in stored procedures and triggers. It's fast, relational,
>searchable, and updatable without any recompiling.
>At the moment I'm considering supporting both SQL Server and Oracle, and it
>seems to me that to support multiple database platforms the business logic
>needs to be moved out of the database layer. Not only would this seem
>necessary to me, but it would also seem to be ugly, much more complicated,
>harder to maintain, and slower, maybe far slower.
Hi Paul,
I disagree. Even when porting to Oracle or any other platform, you'll
still have to have the business logic in the data layer. Just think
back: over the last few years, how many client technologies have you
seen come and go? And how many times did you switch database vendor?
Besides: business rules in the front end tend to be much easier to
bypass than business rules in triggers in the database.

>This is the first time I've looked at this problem; it was my assessment
>years ago that deep business logic (not the minor stuff associated with jus
t
>the UI) in hard code was a bad idea. I was hoping someone could share their
>point of view on this area with me, maybe point out some examples of how
>multiple database support is currently done, and how feasable (automatable)
>it is to port data, with stored procedures and triggers, between SQL and
>Oracle.
It helps if you start coding from day one with the idea that you might
one day have to port. Use ANSI-standard whenever feasible. If you do use
proprietary (because ANSI would require an extremely ugly kludge or many
extra lines of code, or because the ANSI version is lots slower),
document it, and describe how the ANSI version looks.
You'll still have a lot of work carved out for you before you'll be
ready to run the same product on SQL Server and Oracle, becuase there
are lots of irritating differences. Expect the porting of data to be
relatively easy, the porting of queries quite complicated and the
porting of stored procedures and triggers to be hell. Especially if you
didn't write the original code with portability as one of the design
objectives...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7u01j1tqn6tnoaol3siot2te99bl5psnne@.
4ax.com...
<...>
> Besides: business rules in the front end tend to be much easier to
> bypass than business rules in triggers in the database.
<...>
> You'll still have a lot of work carved out for you before you'll be
> ready to run the same product on SQL Server and Oracle, becuase there
> are lots of irritating differences. Expect the porting of data to be
> relatively easy, the porting of queries quite complicated and the
> porting of stored procedures and triggers to be hell. Especially if you
> didn't write the original code with portability as one of the design
> objectives...
Well I guess I'll to stand by my choice to keep most business logic in the
data layer then, and damn the torpeedos, we won't support Oracle.
MSDE is free anyway.
Paul|||"PJ6" <nobody@.nowhere.net> wrote in message
news:eEAw3rrvFHA.2932@.TK2MSFTNGP10.phx.gbl...
... we won't support Oracle.
Great idea... :-)|||>Especially if you
>didn't write the original code with >portability as one of the design
>objectives...
just wanted to add that the price for portability may be very very
high.
For instance, a wide table, appr. 4100 bytes per row, which menas 1 row
per page. Start using those "horrible proprietary" data types like bit
and smalldatatime, and here you go: 3900 bytes, 2 rows per page!
Having sacrificed portasbility, all of a sudden you get 100% better
performance.
There are also plenty of differences in locking and concurrency, index
behaviour etc.|||On Wed, 21 Sep 2005 10:44:26 -0400, PJ6 wrote:

>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:7u01j1tqn6tnoaol3siot2te99bl5psnne@.
4ax.com...
><...>
><...>
>Well I guess I'll to stand by my choice to keep most business logic in the
>data layer then, and damn the torpeedos, we won't support Oracle.
Hi Paul,
If the expected extra revenues do not outweigh the expected cost of
having to maintain two versions of the code plus the write-off of the
conversion to Oracle, that'd be the soundest decision.

>MSDE is free anyway.
And ditto for SQL Express (with even less limitation, IIRC).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 21 Sep 2005 08:45:19 -0700, Alexander Kuznetsov wrote:

>just wanted to add that the price for portability may be very very
>high.
(snip)
>Having sacrificed portasbility, all of a sudden you get 100% better
>performance.
Hi Alexander,
Great example!
That's exactly why I want portability to be "one of" the design
objectives. Not the only one, nor even the most important one.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)