Oracle SQL syntax issue - how do I correctly combine 2 queries without error-CodePudding

So I have 2 queries. The first returns a list of grid sections, with the number of orders for that 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;

This works fine and outputs (code_output_1):

code_output_1

The second query outputs the average quantity of orders per grid section:

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

Again, this works fine and outputs (code_output_2):

code_output_2

What I am trying to achieve is a query which outputs the contents of query 1, where the count(orders.order_pk) column in the main query 1 is equal to the average value calculated in the second query.

I have tried a number of approaches including :

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;

which gives an error (error_1):

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

as well as:

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"

which gives me another error (error_2):

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

If anybody could make any suggestions as to what I'm doing wrong or how to achieve the end result I would be most appreciative.

Thanks.

CodePudding user response：

The intent of the second query is rather unclear to me, but I suspect that you are trying to filter the first resultset (the count of orders per grid section) for the records whose count matches the (rounded) average of the count.

If so, you can use window functions:

SELECT *
FROM (
    SELECT c.grid_section, COUNT(*) cnt_orders,
        AVG(COUNT(*)) OVER() avg_cnt_orders
    FROM customer c
    INNER JOIN orders o ON c.customer_pk = o.customer_fk 
    GROUP BY c.grid_section
) t
WHERE cnt_orders = ROUND(avg_cnt_orders)

Note that I removed the DISTINCTs in your query; they are not needed in an aggregation query such as your first SQL - but your question does not give a lot of details on the cardinality between the different entities, so you might need to adapt this to your actual design.