SQL query for average (2 tables)

[JoshD]

Hi all,



I am wanting to join 2 tables and get the average of a particular field in one table, based off data from another table. For simplicity I have deleted a bunch of fields, but the relationship between the tables remains the same...

What I want is to find the average of HUData from table2 based off of SI_ID from tbl1. As you can see values in SI_ID are not unique, so I want to find the averages of each set of SI_IDs in an SQL query and display AS HUDAverages.

Can this be done? Again I deleted a bunch of other fields, but I kept the relationship the same (one to many for EU_IDs)

Thanks

[ajetrumpet]

what have you tried, Josh?

[JoshD]

Hey Adam,

Well that is the thing, I am not quite sure how to approach this. If the data were in 1 table (say table1) I think it would be something to the effect of

SELECT SI_ID,
SELECT AVG(HUData) AS HUDAverages
FROM table1;

Or something similar. The problem is this can't all be in one table, there are many fields that need to split upon tables for proper design and non- repetition.

I think it is some type of JOIN query, I just can't figure it out.

The other thing is I know that EU_ID is a 9 digit unique number that is stored in table1 one time only, and data points can be entered on that EU_ID multiple times in table2. Not sure if that has any affect on it or not.

Thanks
Josh

[ajetrumpet]

the sql doesnt change from one table to two.

this:

SELECT SI_ID,
SELECT AVG(HUData) AS HUDAverages
FROM table1;

is properly written like this:

SELECT SI_ID, AVG(HUData) AS HUDAverages
FROM table1 GROUP BY SI_ID

if you don't want groupings, you have to replace AVG() with DAVG(). DAVG() though, in a query is not really useful, because it repeats the same number for ever record returned.


so that's the proper sql to write. You can get a JOIN simply by using the query builder. the program does it for you. select fields from two tables. DONE.

[JoshD]

Thank you for the clarification, and wow, I dont know why I didnt just use the Query Wizard all along. That is so easy. I guess I wanted to try to do it manually and learn some SQL that way, but I should probably take the help when I can get it

Thanks again

[ajetrumpet]

marked solved