如何解决SQL错误：Column 'product_aggregation.product_key'选择列表无效

错误根源 #

你遇到的Msg 8120错误，是因为最终查询中使用了聚合函数SUM()，但product_key等列既没有被包含在GROUP BY子句中，也没有被包裹在聚合函数里。

注意看你计算avg_selling_price的代码：

ROUND(SUM(CAST(total_sales AS FLOAT)) / NULLIF(SUM(total_quantity), 0), 1) AS avg_selling_price

而product_aggregation CTE已经是按product_key等字段分组后的结果——每个产品对应唯一一行数据，total_sales和total_quantity都是该产品的汇总值，不需要再用SUM()做二次聚合。

修复后的完整代码 #

Create View gold.report_products As
WITH Base_query AS (
SELECT 
P.product_key, 
P.product_name, 
P.category, 
P.subcategory, 
P.cost,
S.order_date,
S.customer_key,
S.order_number,
S.sales_amount,  
S.quantity
FROM gold.fact_sales S 
LEFT JOIN gold.dim_products P ON S.product_key = P.product_key
WHERE order_date IS NOT NULL
),
product_aggregation AS (
SELECT 
product_key,
product_name,
category,
subcategory,
cost,
DATEDIFF(MONTH, MIN(order_date), MAX(order_date)) AS Lifespan, 
MAX(order_date) AS last_orderdate,
COUNT(DISTINCT order_number) AS total_orders,
COUNT(DISTINCT customer_key) AS total_customers,
SUM(sales_amount) AS total_sales,
SUM(quantity) AS total_quantity
FROM Base_query
GROUP BY 
product_key, 
product_name,
category,
subcategory,
cost
)

--Final Query: combines all product results into one output
SELECT 
product_key,   --line 42
product_name,
category,
subcategory,
cost,

CASE 
WHEN total_sales >= 2430 AND total_sales <= 459438 THEN 'Low Performance'
WHEN total_sales > 459438 AND total_sales <= 916446 THEN 'Mid_Range'
WHEN total_sales > 916446 AND total_sales <= 1373454 THEN 'High Performance'
END AS Revenue_product_segmentation,

total_sales,
total_quantity,
total_orders,
total_customers,
Lifespan,

-- 修正后的avg_selling_price计算：无需SUM，直接用单条记录的汇总值
ROUND(CAST(total_sales AS FLOAT) / NULLIF(total_quantity, 0), 1) AS avg_selling_price,

-- Recency calculation
DATEDIFF(MONTH, last_orderdate, GETDATE()) AS recency_in_month,

-- Avg_order_revenue calculation
CAST(total_sales AS FLOAT) / NULLIF(total_orders, 0) AS Avg_order_revenue,

-- Avg_monthly_revenue calculation
CASE 
WHEN Lifespan=0 THEN total_sales 
ELSE CAST(total_sales AS FLOAT) / Lifespan 
END AS Avg_monthly_revenue

FROM product_aggregation;

关键修正点 #

• 移除了avg_selling_price计算中的SUM()函数，直接使用product_aggregation中已有的total_sales和total_quantity字段进行计算。因为每个产品在product_aggregation中只有一行数据，二次聚合毫无意义，反而会触发分组规则错误。

内容的提问来源于stack exchange，提问作者Ella Ya