How to use group by in SQL?

Question

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

Does this answer your question? [ORA-00979 not a group by expression](https://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression) — OldProgrammer, Oct 27 '21 at 16:55
The selected Territories fields aren't included in the GROUP BY. Standard SQL doesn't allow that. — LukStorms, Oct 27 '21 at 17:27

score 0 · Answer 1 · answered Oct 27 '21 at 17:10

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; 
/

How to use group by in SQL?

1 Answers1