NOT GROUP By issue

Question

I try to make a sum in this formula but I have one issue, not group by expression ?

WHo is the problem ?

SELECT ROUND( SUM( FACT_WLT_AGENT.WLT_LHV_PO_FNGF + 
                   FACT_WLT_AGENT.WLT_LHV_PO_ENA +
                   FACT_WLT_AGENT.WLT_LHV_PO_EEIND +
                   FACT_WLT_AGENT.WLT_LHV_PO_PAQ
              ) /
         3600 * 10
       )
FROM FACT_WLT_AGENT,
     dim_reorganization reorg
WHERE FACT_WLT_AGENT.reorg_id = reorg.reorg_id
  AND is_last_master_reorg    = 'Y'
HAVING FACT_WLT_AGENT.WLT_LHV_PO_FNGF +
       FACT_WLT_AGENT.WLT_LHV_PO_ENA + 
       FACT_WLT_AGENT.WLT_LHV_PO_EEIND +
       FACT_WLT_AGENT.WLT_LHV_PO_PAQ /
       3600 > 0

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, Feb 10 '22 at 19:58

score 1 · Accepted Answer · answered Feb 10 '22 at 20:18

To illustrate the problem, I'll use modified Scott's sample schema.

SQL> select sal, comm from emp where deptno = 10;

       SAL       COMM
---------- ----------
      2450        245
      5000        500
      1300        130

SQL>

This is your current code (which doesn't work):

SQL> select sum(sal + comm) result
  2  from emp
  3  where deptno = 10
  4  having sal + comm > 0;
having sal + comm > 0
       *
ERROR at line 4:
ORA-00979: not a GROUP BY expression


SQL>

Now, you can "fix" it by adding a group by clause, but I don't think that this is what you want:

SQL> select sum(sal + comm) result
  2  from emp
  3  where deptno = 10
  4  group by sal + comm
  5  having sal + comm > 0;

    RESULT
----------
      5500
      2695
      1430

SQL>

I believe that you actually want this - use aggregation in having clause:

SQL> select sum(sal + comm) result
  2  from emp
  3  where deptno = 10
  4  having sum(sal + comm) > 0;

    RESULT
----------
      9625

SQL>

Or, in your case:

SELECT ROUND( SUM( FACT_WLT_AGENT.WLT_LHV_PO_FNGF + 
                   FACT_WLT_AGENT.WLT_LHV_PO_ENA +
                   FACT_WLT_AGENT.WLT_LHV_PO_EEIND +
                   FACT_WLT_AGENT.WLT_LHV_PO_PAQ
              ) /
         3600 * 10
       )
FROM FACT_WLT_AGENT,
     dim_reorganization reorg
WHERE FACT_WLT_AGENT.reorg_id = reorg.reorg_id
  AND is_last_master_reorg    = 'Y'
HAVING sum(FACT_WLT_AGENT.WLT_LHV_PO_FNGF +
           FACT_WLT_AGENT.WLT_LHV_PO_ENA + 
           FACT_WLT_AGENT.WLT_LHV_PO_EEIND +
           FACT_WLT_AGENT.WLT_LHV_PO_PAQ) > 0;

score 0 · Answer 2 · answered Feb 10 '22 at 20:06

group by is required of any field named in either sum or having. In this case you have both, so you need something like this;

SELECT ROUND(SUM(FACT_WLT_AGENT.WLT_LHV_PO_FNGF + FACT_WLT_AGENT.WLT_LHV_PO_ENA + FACT_WLT_AGENT.WLT_LHV_PO_EEIND + FACT_WLT_AGENT.WLT_LHV_PO_PAQ)/ 3600 * 10 )

FROM FACT_WLT_AGENT, dim_reorganization reorg 
WHERE FACT_WLT_AGENT.reorg_id = reorg.reorg_id 
AND is_last_master_reorg = 'Y' 
group by FACT_WLT_AGENT.WLT_LHV_PO_FNGF, FACT_WLT_AGENT.WLT_LHV_PO_ENA, FACT_WLT_AGENT.WLT_LHV_PO_EEIND, FACT_WLT_AGENT.WLT_LHV_PO_PAQ
HAVING FACT_WLT_AGENT.WLT_LHV_PO_FNGF + FACT_WLT_AGENT.WLT_LHV_PO_ENA + FACT_WLT_AGENT.WLT_LHV_PO_EEIND + FACT_WLT_AGENT.WLT_LHV_PO_PAQ / 3600 > 0

score 0 · Answer 3 · answered Feb 10 '22 at 20:22

select statements are [conceptually] evaluated sequentially like this, though one should note that internally, for performance reasons, things are unlikely to actually work this way:

Form the cartesian product of all tables in the from clause.
Apply the join criteria (if any), removing any rows that don't match.
Apply the criteria in the where clause (if any), again removing any rows that don't match.
If a group by clause is present,
- Collapse/Summarize each group to a single row, computing the values of all aggregate function, and removing all columns/expressions that are not in the group by list or are an aggregate function.
- Apply the criteria in the having clause (if any) to filter the result set.

Order the result set by the columns/expressions specified in the order by clause.

You cannot specify a having clause without first having specified a group by clause. Lacking the group by clause, your having clause is syntactically invalid.

The GROUP BY is actually not required to have a HAVING clause. See example in @Littlefoot's answer. — jarlh, Feb 10 '22 at 20:25

score -1 · Answer 4 · answered Feb 10 '22 at 20:29

I'd simply use a derived table, to save some typing and but also to reduce the risk of errors when repeating the same columns over and over again:

SELECT the_sum
FROM
(
    SELECT ROUND( SUM( FACT_WLT_AGENT.WLT_LHV_PO_FNGF + 
                   FACT_WLT_AGENT.WLT_LHV_PO_ENA +
                   FACT_WLT_AGENT.WLT_LHV_PO_EEIND +
                   FACT_WLT_AGENT.WLT_LHV_PO_PAQ
              ) / 3600 * 10) as the_sum
    FROM FACT_WLT_AGENT
    JOIN dim_reorganization reorg
      ON FACT_WLT_AGENT.reorg_id = reorg.reorg_id
    WHERE is_last_master_reorg    = 'Y
) dt
WHERE the_sum > 0;

Note the explicit JOIN syntax! Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed!

NOT GROUP By issue

4 Answers4