NOT GROUP By issue-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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!