CODEWITHSUNDEEP
sqloracle-databasegroup-by
user13541818
user13541818

Reputation:

Why I can not use ORDER BY and GROUP BY at same time

I have simple query like

        SELECT sc.* 
        FROM sub_category sc
        INNER JOIN submaterial s ON s.ParentID = sc.Id
        INNER JOIN project_material_suppliers pms ON pms.CateogryID = s.MaterialCategory 
        INNER JOIN suppliers  s ON s.Id = pms.SupplierID 
        INNER JOIN deliverer d ON d.EmailID = s.DelivererEmailId
        WHERE sc.ParentID = p_id AND pms.ProjectID = p_projectId       
        order by sc.OrderIndex

In this case it works, but problem is when I want to use GROUP BY in same time before ORDER BY something like this

        SELECT sc.* 
        FROM sub_category sc
        INNER JOIN submaterial s ON s.ParentID = sc.Id
        INNER JOIN project_material_suppliers pms ON pms.CateogryID = s.MaterialCategory 
        INNER JOIN suppliers  s ON s.Id = pms.SupplierID 
        INNER JOIN deliverer d ON d.EmailID = s.DelivererEmailId
         WHERE sc.ParentID = p_id AND pms.ProjectID = p_projectId       
        GROUP BY sc.Id
        ORDER BY sc.OrderIndex

Output error which I get

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 147 Column: 11

What is wrong here ? Where did I made mistake ?

Upvotes: 0

Views: 119

Answers (2)

Littlefoot
Littlefoot

Reputation: 142968

It is not that you can't; you can, but only if you do it properly.

This is a regular situation:

SQL> select deptno, sum(sal) from emp group by deptno order by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        10      13750
        20      10995
        30       9400

This will fail (as you know):

SQL> select deptno, sum(sal) from emp group by deptno order by job;
select deptno, sum(sal) from emp group by deptno order by job
                                                          *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

But, if you include offending column into the GROUP BY clause, it'll work - and produce different result as there's a new column you're grouping data by:

SQL> select deptno, sum(sal) from emp group by deptno, job order by job;

    DEPTNO   SUM(SAL)
---------- ----------
        20       6000
        20       2020
        30        950
        10       1300
        10       2450
        20       2975
        30       2850
        10      10000
        30       5600

9 rows selected.

SQL>

Besides, query you wrote - the one that uses group by clause - will fail anyway as you can't group by only one column, without aggregating the rest of them:

SQL> select * from emp group by deptno;
select * from emp group by deptno
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Upvotes: 1

KayaNatsumi
KayaNatsumi

Reputation: 414

Your problem is not only in ORDER BY but also in SELECT sc.*, when you use GROUP BY you can only SELECT or ORDER BY grouped columns and rest has to be somehow aggregated. See aggregation documentation Doc. Aggregate Functions

Example:

SELECT sc.Id, sc.OrderIndex, MAX(sc.some_other_column_example)
  FROM sub_category sc
 INNER JOIN submaterial s
    ON s.ParentID = sc.Id
 INNER JOIN project_material_suppliers pms
    ON pms.CateogryID = s.MaterialCategory
 INNER JOIN suppliers s
    ON s.Id = pms.SupplierID
 INNER JOIN deliverer d
    ON d.EmailID = s.DelivererEmailId
 WHERE sc.ParentID = p_id
   AND pms.ProjectID = p_projectId
 GROUP BY sc.Id, sc.OrderIndex
 ORDER BY sc.OrderIndex;

Upvotes: 0

Related Questions