CODEWITHSUNDEEP
sqloracle-databaseerror-handlingsum
Sh_ar
Sh_ar

Reputation: 23

group function is not allowed here error

For each branch with more than 5 employees, find number of product in each branch and sum of their purchases.

SELECT SUM(ORDERS.PRODUCT_NO) AS Products_Sold , 
SUM(ORDERLINE.ORDERLINE_NO) AS Total_Purchases, SUM(EMPLOYEE.EMPLOYEE_NO)
FROM BRANCH JOIN ORDERS
ON BRANCH.BRANCH_NO = ORDERS.BRANCH_NO
JOIN ORDERLINE
ON ORDERS.ORDER_NO = ORDERLINE.ORDER_NO
JOIN EMPLOYEE
ON BRANCH.BRANCH_NO = EMPLOYEE.BRANCH_NO
GROUP BY (SUM(ORDERS.PRODUCT_NO), SUM(ORDERLINE.ORDERLINE_NO), 
SUM(EMPLOYEE.EMPLOYEE_NO))
HAVING SUM(EMPLOYEE_NO) >= 5;

and I got this error

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

Upvotes: 1

Views: 2939

Answers (2)

APC
APC

Reputation: 146239

It seems highly unlikely that summing primary keys is the answer your teaching is expecting.

Somewhere on ORDERLINE there must be a column or columns representing the value of the purchase; perhaps theere is a column called ORDER_VALUE or perhaps you need to multiple the PRODUCT.COST with the ORDER_LINE.QUANTITY. The same goes for calculating teh number of employees in a branch. You need to count them not sum their primary keys.

The requirement is fuzzy so this solution counts the number of distinct products sold as well as the total number of orders. However, the data model (as expressed in your query) is odd: normally we would expect the Product to be on the Order Line, because an Order can have more than one Product (that's why we have Lines).

So, this query shows you how to do things but may not produce the expected answer. You will need to compare it to your table structures and modify it accordingly. 

select branch.branch_no
       , count(distinct orders.product_no) as no_of_products sold
       , count(distinct orders.order_no) as no_of_orders , 
       , sum(orderline.amt * product.product_cost) as total_purchase_amt
from (select branch.*
      from branch
          join employee
               on branch.branch_no = employee.branch_no
      group by brancn.branch_no 
      having count(employee_no) >= 5 ) branch 
join orders
    on branch.branch_no = orders.branch_no
join orderline
    on orders.order_no = orderline.order_no
join product
    on orders.product_no = product.product_no
group by branch.branch_no;

Upvotes: 0

Sh_ar
Sh_ar

Reputation: 23

SELECT SUM(ORDERLINE.PRODUCT_NO) AS Products_Sold , SUM(ORDERLINE.ORDERLINE_NO) AS Total_Purchases
FROM BRANCH JOIN ORDERS
ON BRANCH.BRANCH_NO = ORDERS.BRANCH_NO
JOIN ORDERLINE
ON ORDERS.ORDER_NO = ORDERLINE.ORDER_NO
JOIN EMPLOYEE
ON BRANCH.BRANCH_NO = EMPLOYEE.BRANCH_NO
GROUP BY ORDERLINE.PRODUCT_NO, ORDERLINE.ORDERLINE_NO, EMPLOYEE.EMPLOYEE_NO
HAVING SUM(EMPLOYEE_NO) >= 5;

Upvotes: 1

Related Questions