In SQL, the AVG function is used to calculate the average value of a column in a table. In scenarios where you need to combine multiple tables, you can use the JOIN clause to fetch data from different tables based on a common column or key.

Let’s see an example where we want to calculate the average rating of a product based on the ratings given in one table and the product details stored in another table.

Tables structure

Table: products

Table: ratings

SQL Query

To calculate the average rating of products, we can use the AVG function in combination with the JOIN clause. Here’s an example query:

SELECT products.product_id, products.name, AVG(ratings.rating) AS average_rating
FROM products
JOIN ratings ON products.product_id = ratings.product_id
GROUP BY products.product_id, products.name;

Let’s analyze the query step by step:

1. We specify the columns we want to select: products.product_id, products.name, and the average rating using the AVG(ratings.rating) function.
2. We specify the tables we want to join: products table with ratings table using the common column product_id.
3. We group the result by products.product_id and products.name to ensure we get the average rating for each individual product.
4. Finally, we execute the query and retrieve the average ratings for each product.

The expected result of the query would be:

In conclusion, by combining the AVG function and JOIN clause, you can accurately calculate the average value of a column from multiple tables in SQL.