Oracle SQL语法问题：如何合并查询获取订单数等于平均值的结果？

我有两个Oracle SQL查询：

1. 第一个查询返回各网格区域（grid_section）及其对应的订单数量：

SELECT DISTINCT customer.grid_section, count(orders.order_pk)
FROM customer INNER JOIN orders
    ON customer.customer_pk = orders.customer_fk 
GROUP BY customer.grid_section;

该查询可正常输出结果。

2. 第二个查询计算每个网格区域的平均订单数量：

SELECT ROUND(
        (COUNT(DISTINCT orders.order_pk)) / 
        (COUNT(DISTINCT customer.grid_section))) as avg
FROM customer, orders;

该查询也可正常输出结果。

我需要实现一个查询，输出第一个查询中订单数等于第二个查询计算出的平均值的网格区域及其订单数，但尝试两种方法均报错：

方法一及报错 #

SELECT DISTINCT customer.grid_section, count(orders.order_pk)
FROM customer INNER JOIN orders
    ON customer.customer_pk = orders.customer_fk
WHERE count(orders.order_pk)
    (SELECT ROUND(
        (COUNT(DISTINCT orders.order_pk)) / 
        (COUNT(DISTINCT customer.grid_section))) as avg
    FROM customer, orders   
    )
GROUP BY customer.grid_section;

报错信息：

ORA-00934: group function is not allowed here
00934. 00000 -  "group function is not allowed here"
*Cause:    
*Action:
Error at Line: 4 Column: 7

方法二及报错 #

SELECT DISTINCT customer.grid_section, 
    COUNT(orders.order_pk) AS "tot_orders"
FROM customer, orders, (SELECT ROUND(
        (COUNT(DISTINCT orders.order_pk)) / 
        (COUNT(DISTINCT customer.grid_section))) AS "avg_orders"
        FROM customer, orders) subq1
WHERE "tot_orders" = "avg_orders"

报错信息：

ORA-00904: "tot_orders": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 7 Column: 7

方法一错误原因 #

1. WHERE子句无法直接使用聚合函数（如count(orders.order_pk)）：WHERE是分组前筛选行的逻辑，聚合函数是分组后计算的结果，语法上不允许提前引用分组后的值。
2. WHERE子句缺少比较运算符（应该写为= (子查询)），属于语法遗漏。

方法二错误原因 #

1. 使用隐式笛卡尔积（FROM customer, orders）未加关联条件，会导致数据重复计算，结果完全失真。
2. WHERE子句不能引用SELECT列表定义的别名（tot_orders）：SQL执行顺序中WHERE先于SELECT执行，此时别名还未生成，无法识别。
3. 未添加GROUP BY子句，COUNT(orders.order_pk)会计算所有订单总数，而非按网格分组的数量。

推荐两种可行方案：

方案一：HAVING子句结合子查询 #

利用HAVING在分组后筛选的特性，直接引用聚合函数与平均值子查询对比：

SELECT customer.grid_section, COUNT(orders.order_pk) AS tot_orders
FROM customer 
INNER JOIN orders ON customer.customer_pk = orders.customer_fk
GROUP BY customer.grid_section
HAVING COUNT(orders.order_pk) = (
    SELECT ROUND(
        COUNT(DISTINCT orders.order_pk) / 
        COUNT(DISTINCT customer.grid_section)
    )
    FROM customer 
    INNER JOIN orders ON customer.customer_pk = orders.customer_fk
);

注意：原第二个查询用笛卡尔积会导致计算错误，这里改为和第一个查询一致的INNER JOIN关联逻辑，保证平均值计算准确。

方案二：CTE分步计算 #

用公共表表达式（CTE）分步计算网格订单数和平均值，逻辑更清晰，性能更优：

WITH grid_order_counts AS (
    SELECT customer.grid_section, COUNT(orders.order_pk) AS tot_orders
    FROM customer 
    INNER JOIN orders ON customer.customer_pk = orders.customer_fk
    GROUP BY customer.grid_section
),
avg_order_count AS (
    SELECT ROUND(AVG(tot_orders)) AS avg_orders
    FROM grid_order_counts
)
SELECT g.grid_section, g.tot_orders
FROM grid_order_counts g, avg_order_count a
WHERE g.tot_orders = a.avg_orders;

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