none
Average from one table to another RRS feed

  • Question

  • Hi,

    I'm new to SQL and trying to understand the logic of working with more than one table.

    I wish to take the average value of one table's column and UPDATE it as the value for a whole column on another table. 

    For example: 

    Table Band: Id, name, Height

    Table Whatever: Id, BandAvg

    I wish to set the average height from table Band to column 'BandAvg'.

    What shall be the best way? is there a way to set a variable that both tables can access or is there any better way to do so?

    Thanks in advance!

    Tuesday, November 12, 2019 6:52 AM

Answers

  • And, the below is the another possible way to achieve the same.

    WITH CTE AS
    (SELECT 
    	Id
    	,AVG(Height) BandAvg
    FROM Band
    GROUP BY Id)
    
    UPDATE tbla
    SET tbla.BandAvg = tblb.BandAvg
    FROM Whatever tbla
    INNER JOIN CTE tblb
    ON tbla.Id = tblb.Id

    Hope this is helpful !!
    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, November 12, 2019 7:23 AM

All replies

  • Hi

    Below way of doing this can help you to fulfill your requirement.

    UPDATE tbla
    SET tbla.BandAvg = tblb.BandAvg
    FROM Whatever tbla
    INNER JOIN
    	(SELECT 
    		Id
    		,AVG(Height) BandAvg
    	FROM Band
    	GROUP BY Id)tblb
    ON tbla.Id = tblb.Id

    Hope this is helpful !!
    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, November 12, 2019 7:22 AM
  • And, the below is the another possible way to achieve the same.

    WITH CTE AS
    (SELECT 
    	Id
    	,AVG(Height) BandAvg
    FROM Band
    GROUP BY Id)
    
    UPDATE tbla
    SET tbla.BandAvg = tblb.BandAvg
    FROM Whatever tbla
    INNER JOIN CTE tblb
    ON tbla.Id = tblb.Id

    Hope this is helpful !!
    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, November 12, 2019 7:23 AM
  • Thanks! worked out for me :)
    Tuesday, November 12, 2019 8:27 AM
  • Hi

    Do click on "Mark as Answer" and Upvote on the posts that helps you , this can be beneficial to other community members.

    Thank you

    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, November 12, 2019 9:06 AM
  • Hi GBTiger,

    I am glad to know that your problem has been solved.

    In order to close this thread, please mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 13, 2019 2:39 AM