Results 1 to 3 of 3

Thread: SQL Avg Query

  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: SQL Avg Query

    Hi all,

    I am wanting to set up a query to 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. Values in SI_ID are not unique, so I want to find the averages of HUData per 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
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    CREATE TABLE table1
    ( eu_id ... PRIMARY KEY
    , si_id ...
    , gename ...
    );
    CREATE TABLE table2
    ( id ... PRIMARY KEY
    , eu_id ...
    , hudata ...
    );

    not sure a word document was necessary for this information, but it sure is purty
    Code:
    SELECT table1.si_id
         , AVG(table2.hudata) AS hudaverages
      FROM table1
    INNER
      JOIN table2
        ON table2.eu_id = table1.eu_id
    GROUP
        BY table1.si_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    2
    Thank you, that is perfect. It also clears the way to create additional queries in this database that are similar.

    Question, if I wanted to add a standard deviation column next to it for the same field, would it look like this?

    SELECT table1.si_id
    , AVG(table2.hudata) AS hudaverages
    , STDEV(table2.hudata) AS hudStdDev
    FROM table1
    INNER
    JOIN table2
    ON table2.eu_id = table1.eu_id
    GROUP
    BY table1.si_id
    Last edited by JoshD75; 04-13-11 at 10:46.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •