DECLARE 
    CURSOR cur_r IS 
(line 3)        SELECT  
            Territories.TerritoryID, 
            Territories.TerritoryDescription, 
            count(orders.orderID) as sales, 
            to_char(orders.orderDate,'MON') 
        FROM  
     
            Territories
        INNER JOIN Orders 
            on  Territories.TerritoryID=orders.TerritoryID
        GROUP BY 
            to_char(orders.orderDate,'MON') 
        ORDER BY 
            sales;
        RES cur_r%ROWTYPE;
        
BEGIN  
(line 20)OPEN cur_r;
         LOOP
        FETCH cur_r INTO RES;
        exit WHEN cur_r%NOTFOUND;
        dbms_output.Put_line(
                             'Employee ID : '
                             ||  RES.TerritoryID
                             || ' Date of Hire : '
                             ||  RES.TerritoryDescription
    
                             );
    END LOOP;
    CLOSE cur_r;                        
END; 
/

Why do I get errors like below

ORA-00979: not a GROUP BY expression ORA-06512: at line 3 ORA-06512: at line 20 ORA-06512: at "SYS.DBMS_SQL", line 1721

CodePudding user response：

DECLARE 
    CURSOR cur_r IS 
(line 3)        SELECT  
            Territories.TerritoryID, 
            Territories.TerritoryDescription, 
            count(orders.orderID) as sales, 
            to_char(orders.orderDate,'MON') 
        FROM  
     
            Territories
        INNER JOIN Orders 
            on  Territories.TerritoryID=orders.TerritoryID
        GROUP BY 
            to_char(orders.orderDate,'MON'), 
            Territories.TerritoryID,
            Territories.TerritoryDescription,
            orders.orderID
        ORDER BY 
            sales;
        RES cur_r%ROWTYPE;
        
BEGIN  
(line 20)OPEN cur_r;
         LOOP
        FETCH cur_r INTO RES;
        exit WHEN cur_r%NOTFOUND;
        dbms_output.Put_line(
                             'Employee ID : '
                             ||  RES.TerritoryID
                             || ' Date of Hire : '
                             ||  RES.TerritoryDescription
    
                             );
    END LOOP;
    CLOSE cur_r;                        
END; 
/