ORACLE SQL Combining many rows to one and sum a field

ORACLE SQL … I am trying to combine rows into one when they share the same contents in the following fields:

ENTITY_UNIQUE_IDENTIFIER
ACCOUNT_NUMBER
AB_CODE_B
ACCOUNT_DESCRIPTION
ACCOUNT_TYPE 

**AND** when FINANCIAL_STATEMENT = 'A' 

In addition, I need the sum of the fields GL_ACCOUNT_BALANCE from all the respective previous rows to be totaled onto that one new row.

I have written the following code.

Select ab.company ENTITY_UNIQUE_IDENTIFIER,ab.account ACCOUNT_NUMBER,
  CASE
      When Substr(ab.account,1,1) in ('1','2','3') or Substr(ab.account,1,4) = '9999' Then ''
      When Substr(ab.account,1,1) NOT in ('1','2','3') or Substr(ab.account,1,4) != '9999' Then ab.CODE_B
   End  ab_CODE_B,
   CASE
      When Substr(ab.account,1,1) in ('1','2','3') or Substr(ab.account,1,4) = '9999' Then ''
      When Substr(ab.account,1,1) NOT in ('1','2','3') or Substr(ab.account,1,4) != '9999' Then ab.CODE_B_DESC
   End  ab_CODE_B,
   ' ' KEY_5,
   ' ' KEY_6,
   ' ' KEY_7,
   ' ' KEY_8,
   ' ' KEY_9,
   ' ' KEY_10,
   a.description ACCOUNT_DESCRIPTION,
   ' ' ACCOUNT_REFERENCE,
   Case
      When Substr(ab.account,1,1) in ('1','2','3') or Substr(ab.account,1,4) = '9999' Then 'A'
      Else 'I'
   End FINANCIAL_STATEMENT,----
   Case
     When (ab.account_type = 'ASSETS' or ab.account_type = 'ASSETS1' or ab.account_type = 'ASSETS2'
       or ab.account_type = 'ASSETS3') Then 'Asset'
     When ab.account_type = 'LIABILITIES' or ab.account_type = 'LIABILITIES1' Then 'Liability'
     When ab.account_type = 'EXPENSE' or ab.account_type = 'EXPENSE1' or ab.account_type = 'EXPENSE2'
       Or ab.account_type = 'COGS' Then 'Expense'
     When ab.account_type = 'REVENUES' Then 'Revenue'
     When ab.account_type = 'EQUITY' Then 'Equity'
   Else ab.account_type
   End ACCOUNT_TYPE,
   'TRUE' ACTIVE_ACCOUNT,
   Case
     When Sum(ab.debet_balance) + Sum(ab.credit_balance) = 0 Then 'FALSE'
       Else 'TRUE'
   End ACTIVITY_IN_PERIOD,
   ' ' ALTERNATE_CURRENCY,
   Case
     When ab.company = 'ZZZZ' Then 'USD'
     When ab.company = 'SSSS' Then 'CAD'
     When ab.company = 'wwww' Then 'EUR'
     When ab.company = 'wwwwE' Then 'USD'
     When ab.company = 'QWQW' Then 'CNY'
   End ACCOUNT_CURRENCY,
   To_Char(Trunc(Last_Day(Sysdate - 10)),'mm/dd/yyyy') PERIOD_END_DATE,
   ' ' GL_REPORTING_BALANCE,
   Sum(ab.amount_balance) GL_ACCOUNT_BALANCE,
   ' ' ACCOUNT_REFERENCE2,
   ' ' ACCOUNT_REFERENCE3,
   ' ' ACCOUNT_REFERENCE4,
   ' ' ACCOUNT_REFERENCE5,
   ' ' ACCOUNT_REFERENCE6
From account a
Left Outer Join accounting_balance_budget ab
  On ab.account = a.account
  And ab.company = a.company
Where ab.accounting_year = Extract(year from Sysdate - 10)
  And ab.accounting_period <= Extract(Month from Sysdate - 10)
  And Sysdate between a.valid_from and a.valid_until
  And ab.company in ('ZZZZ','SSSS','wwww','wwwwE','QWQW')
  And (Substr(ab.account,1,1) in ('1','2','3','4','5','6','7','8') or Substr(ab.account,1,4) = '9999')
Group by ab.company,ab.account,ab.company, ab.account_type, ab.accounting_year,ab.code_b,ab.CODE_B_DESC, a.accnt_group, a.description
HAVING Sum(ab.amount_balance) >0
Order by 2, 1;

Answer

From my point of view, code you posted is too complex. You should have posted something simpler.

Anyway: if I understood you correctly, you’re looking for LISTAGG function which “aggregates” column values (lists them, one after another, in a single column), along with the SUM function (to calculate balance summary).

Sample code:

  SELECT entity_unique_identifier,
         account_number,
         ab_code_b,
         account_description,
         account_type,
         --
         LISTAGG (some_other_column, ', ') WITHIN GROUP (ORDER BY NULL) some_other_column,
         SUM (gl_account_balance) sum_gl_account_balance
    FROM ...
   WHERE ...
GROUP BY entity_unique_identifier,
         account_number,
         ab_code_b,
         account_description,
         account_type;

As you can see, columns that aren’t aggregated have to be listed in the GROUP BY clause.

---

A simple example, based on Scott’s EMP table:

SQL> select deptno,
  2    job,
  3    listagg(ename, ', ') within group (order by null) enames,
  4    sum(sal) sum_salary
  5  from emp
  6  group by deptno,
  7           job;

    DEPTNO JOB       ENAMES                                   SUM_SALARY
---------- --------- ---------------------------------------- ----------
        10 CLERK     MILLER                                         1300
        10 MANAGER   CLARK                                          2450
        10 PRESIDENT KING                                           5000
        20 CLERK     ADAMS, SMITH                                   1900
        20 ANALYST   FORD, SCOTT                                    6000
        20 MANAGER   JONES                                          2975
        30 CLERK     JAMES                                           950
        30 MANAGER   BLAKE                                          2850
        30 SALESMAN  ALLEN, MARTIN, TURNER, WARD                    5600

9 rows selected.

SQL>