在每个子值旁边显示父值的平均值 [英] Display average value of parent values next to each sub value

问题描述

SQL 小提琴

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");

在表中我有不同的campaigns 和相应的campaing_categories.
现在我想获取每个 Campaign_Category 的 average_sales_price 并将其显示在每个 Campaign_ID 旁边.
预期结果应如下所示:

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

通过以下查询，我只能获得每个 Campaign_ID 的 average_sales_price:

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;

如何修改我的查询以在每个 Campaign_ID 旁边显示相应 Campaign_Category 的 average_sales_price?

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

推荐答案

可以使用关联子查询:

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;

这里是 SQL Fiddle.

注意:不要在GROUP/ORDER BY 子句中使用列位置，如果在SELECT 语句中更改列位置，则查询将无法生成预期结果.因此，全部限定列名.

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.

这篇关于在每个子值旁边显示父值的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！