Display average value of parent values next to each sub value

SQL Fiddle

CREATE TABLE Campaigns (
    Campaign_ID VARCHAR(255),
    Campaign_Category VARCHAR(255),
    Sales_Volume VARCHAR(255),
    Sales_Quantity VARCHAR(255)
);

INSERT INTO Campaigns
(Campaign_ID, Campaign_Category, Sales_Volume, Sales_Quantity)
VALUES 
("C001", "Fashion", "500", "10"),
("C002", "Fashion", "100", "20"),
("C003", "Sport", "800", "15"),
("C004", "Sport", "300", "90"),
("C005", "Sport", "700", "80"),
("C006", "Shoes", "200", "100"),
("C007", "Shoes", "400", "50");

In the table I have different campaigns and corresponding campaing_categories.
Now I want to get the average_sales_price per Campaign_Category and display this it next to each Campaign_ID.
The expected result should look like this:

CampaingID     Campaign Category    average_sales_price
C001             Fashion                  20
C002             Fashion                  20
C003             Sport                    9.72
C004             Sport                    9.72
C005             Sport                    9.72
C006             Shoes                    4
C007             Shoes                    4

With the following query I only get the average_sales_price per Campaign_ID:

SELECT
Campaign_ID,
Campaign_Category,
SUM(Sales_Volume) / SUM(Sales_Quantity) AS Average_Sales_Price
FROM Campaigns
GROUP BY 1;

How do I have to modify my query to display the average_sales_price of the corresponding Campaign_Category next to each Campaign_ID?

Answer

You can use correlated sub-query :

SELECT C.Campaign_ID, C.Campaign_Category,
       (SELECT SUM(CC.Sales_Volume) / SUM(CC.Sales_Quantity) 
        FROM Campaigns CC
        WHERE CC.Campaign_Category = C.Campaign_Category
       ) AS average_sales_price
FROM Campaigns C;

Here is SQL Fiddle.

Note : Do not use column position in GROUP/ORDER BY clause, if you change the column position in SELECT statement then query will fail to generate expected result. So, all qualify column name instead.

Advertisement

Answer