Get aggregated average values joining a second table and display them next to each value in first table

user_4934150 0
Viewed 50

I have two tables which you can also find in the SQL fiddle:

CREATE TABLE Sales (
    Product_ID VARCHAR(255),
    Category_ID VARCHAR(255),
    Sales_Value VARCHAR(255),
    Sales_Quantity VARCHAR(255)
);
INSERT INTO Sales
(Product_ID, Category_ID, Sales_Value, Sales_Quantity)
VALUES 
("P001", "C001", "500", "200"),
("P002", "C001", "600", "100"),
("P003", "C002", "300", "250"),
("P004", "C002", "900", "400"),
("P005", "C002", "800", "600"),
("P006", "C003", "200", "150"),
("P007", "C003", "700", "550");


CREATE TABLE Categories (
    Category_ID VARCHAR(255),
    Category_Name VARCHAR(255)
);
INSERT INTO Categories
(Category_ID, Category_Name)
VALUES 
("C001", "Fashion"),
("C002", "Sport"),
("C003", "Shoes");

The first table contains the Sales for each product.
The second table contains the Categories.

Now, I want to display all products and the average_sales_price_per_category next to each product.
The result should look like this:

Product_ID      Category      average_sales_price_per_category
P001             Fashion               3.66
P002             Fashion               3.66
P003             Sport                 1.60
P004             Sport                 1.60
P005             Sport                 1.60
P006             Shoes                 1.28
P007             Shoes                 1.28

I tried to go with the solution from this question but I think the OVER clause is not available in my MySQL version:

SELECT 
s.Product_ID, 
c.Category_Name,
((SUM(s.Sales_Value * s.Sales_Quantity) over (partition BY c.Category_ID) /
SUM(s.Sales_Value) over (partition BY c.Category_ID)) as average_sales_price
FROM Sales s
JOIN Categories c ON c.Category_ID = s.Category_ID;

Error:

check the manual that corresponds to your MySQL server version for the right syntax to use near 'over (partition BY c.Category_ID) / SUM(s.Sales_Value) over (partition BY c.Cate' at line 4

What other SQL can I use to get the expected result?

mysql sql
3 Answers

You should be using AVG() here as an analytic function:

SELECT 
    s.Product_ID,
    c.Category_Name,
    AVG(s.Sales_Value * s.Sales_Quantity) OVER (PARTITION BY c.Category_ID) AS average_sales_price
FROM Sales s
INNER JOIN Categories c
    ON c.Category_ID = s.Category_ID;

The exact error you are having is coming from using SUM() in the denominator as an aggregate function, without using GROUP BY. Another problem with what you were trying to do is that the total sum could be zero, which would lead to an error in your query. Rather than trying to fix your current approach, you should use AVG.

Demo

user_18632290

You can get the expected result using a subquery:

SELECT 
  s.Product_ID,
  ca.Category_Name,
  ROUND(average_sales_price, 2) as average_sales_price
FROM Sales s
JOIN (
  SELECT 
    c.Category_ID,
    c.Category_Name,
    AVG(s.Sales_Value / s.Sales_Quantity) as average_sales_price
  FROM Sales s
  JOIN Categories c ON c.Category_ID = s.Category_ID
  GROUP BY s.Category_ID 
) ca
ON ca.Category_ID = s.Category_ID

The derived table ca is an enriched Category table with sale price average for each category, so you can join this derived table instead of the original Category.

user_30081850

For older version, you can use correlated sub-query : 

SELECT s.Product_ID, c.Category_Name,
       (SELECT SUM(SS.Sales_Value) / SUM(SS.Sales_Quantity)
        FROM Sales SS 
        WHERE SS.Category_ID = S.Category_ID
       ) AS average_sales_price
FROM Sales s JOIN 
     Categories c 
     ON c.Category_ID = s.Category_ID;

Here is SQL Fiddle.

user_55294450
Related