Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509

    Question Unanswered: SQLPLUS REPORT- Display a column ONLY at the break

    What I am trying to do is to use sqlPlus reporting and suppress the column on the detail lines, but display the summed value on the break line. I can easily do something like

    Code:
    break on dept
    compute sum of total on dept
    
    select dept,emp_cd,salary,bonus,salary+bonus total
    group by dept
    order by dept,emp_cd;
    
    Dept   emp_cd    salary    bonus    total
    ----- ------- --------- -------- --------
    deptA     123    123.45    30.00   153.45
              234    100.00    15.00   115.00
    *****                            --------
    sum                                268.45
    
    What I want to see is
    
    
    Dept   emp_cd    salary    bonus    total
    ----- ------- --------- -------- --------
    deptA     123    123.45    30.00
              234    100.00    15.00
    *****                            --------
    sum                              268.45
    Any help would be appreciated.
    Last edited by beilstwh; 03-15-07 at 17:56.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't think there is a way to do precisely that, but you could perhaps experiment with this approach:
    Code:
    SQL> select NVL(ename,'sum') ename, case when ename is null then sal end sal
      2  from
      3  ( select ename, sum(sal) sal
      4    from emp
      5    group by cube(ename)
      6  ) v
      7  order by ename;
    
    ENAME             SAL
    ---------- ----------
    ADAMS
    ALLEN
    BLAKE
    CLARK
    FORD
    JAMES
    JONES
    KING
    MARTIN
    MILLER
    SCOTT
    SMITH
    TURNER
    WARD
    sum             29025

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    This works on 9i and above.
    Code:
    SQL>
    SQL> select ename, sal, comm,
      2         case when grouping_id( sal ) = 1
      3              then sum( nvl( sal, 0 ) + nvl( comm, 0 ) )
      4          end total
      5    from emp
      6   group by grouping sets ( ( ename, sal, comm ), () )
      7  /
    
    ENAME             SAL       COMM      TOTAL
    ---------- ---------- ---------- ----------
    FORD             3000
    KING             5000
    WARD             1250        500
    ADAMS            1100
    ALLEN            1600        300
    BLAKE            2850
    CLARK            2450
    JAMES             950
    JONES            2975
    SCOTT            3000
    SMITH             800
    MARTIN           1250       1400
    MILLER           1300
    TURNER           1500          0
                                          31225
    
    15 rows selected.
    
    SQL>

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Thank you all, I brow beat the user into accepting having the total on every line. However, this is good stuff and I appreciate the techniques for future development.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •