Expressions of Totals from a Table

[Alan Balthrop]

Hopefully this will be my last question of the weekend, after which I
will leave the newsgroup alone for a while


Yesterday I posted that one of several of my goalkeeping statistical
catagories are expressions (Save Percentage, Goals Against Average,
and Winning Percentage). My formulas work fine when I querry
individual season statistics:

example: 1996 season SQL statements written by Access 2002:

SELECT gk.[No], gk.Player, gk.GP, gk.MIN, gk.SHF, gk.SV,
IIf([SV]+[GA]>0,[SV]/([SV]+[GA]),0) AS SVPCT, gk.GA, ([GA]*60)/[MIN]
AS GAA, gk.W, gk.L, IIf(([W]+[L])>0,[W]/([W]+[L]),0) AS WPCT
FROM gk
WHERE (((gk.MIN)>0.01) AND ((gk.Season)=12))
ORDER BY gk.MIN DESC , ([GA]*60)/[MIN];


but when I try to use the same formula for SVPCT ([SV]/([SV]+[GA]) on
a "sum" of of the all seasons for a player (ex: SUM of SV, of SUM of
GA), I get either a syntax error if I do not give it an entry in the
totals field, or the wrong result if I use any (average, max, sum,
etc) of the totals.


Thanks for one last piece of help for the weekend!

 

[Michel Walsh]

Hi,


If you use an aggregate, like SUM, you should care that each SELECTed
field must either be aggregated, either appear in the GroupBy, either be an
arithmetic combination of fields that are in the first two categories.


SELECT SUM(note), PlayerID, SUM(Note)/COUNT(Note)
FROM myTable
GROUP BY PlayerID

is fine, but


SELECT SUM(note), PlayerID, PlayerFirstName, SUM(Note)/COUNT(Note)
FROM myTable
GROUP BY PlayerID


is NOT! PlayerFirstName is neither aggregated, neither in the GROUP BY. A
possible solution is add it to the GROUP BY:

SELECT SUM(note), PlayerID, PlayerFirstName, SUM(Note)/COUNT(Note)
FROM myTable
GROUP BY PlayerID, PlayerFirstName


so, each "select"ed thing is now either aggregated, either in the GroupBy,
either an expression involving the first two categories.



Hoping it may help,
Vanderghast, Access MVP