GROUP BY Avg and Sum

pinkandthebrain · July 13th, 2003, 07:04 PM

Just wondering what the best way is of accomplishing this. I want to get the Average by Location and join to another file and display the total (Sum) for all locations on each row.
Table1:
SELECT TLoc TDate TValue FROM Table1
Table2:
SELECT BLoc Avg(BValue) FROM Table2 GROUP BY BLoc
In the end what I want is the Sum(BValue) for all locations displayed on each row from Table1. Is there a function available, or do I need to setup a dummy field in both cases that allows me to group by all locations. Thanks.

David Cameron · July 13th, 2003, 09:19 PM

How do the two tables relate to eachother?

regards
David Cameron

pinkandthebrain · July 14th, 2003, 01:44 AM

The only thing they have in common is location. TLoc and BLoc

Jeff Mason · July 14th, 2003, 09:20 AM

Your 'specification' is a bit unclear to me.

Quote:

quote:
what I want is the Sum(BValue) for all locations displayed on each row from Table1

I've read this several times and I still am not sure what you really want. I'm not sure what the relationship between the two tables has to do with all this.

Sorry. I'll just guess you want the Average BValue by location from Table2 and the sum of BValue from Table2 for all locations, and that these two values are to be returned along with the other data in each row in Table1. The two tables have some kind of relationship based on TLoc=BLoc. I have no idea what the primary keys are:

Code:

SELECT TLoc TDate TValue,
    (SELECT AVG(BValue) FROM Table2 WHERE Table2.BLoc=T1.Tloc) as LocAvg,
    (SELECT SUM(BValue) FROM Table2) as TotalAllLocs
 FROM Table1 T1;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com