13 Replies Latest reply: Sep 2, 2005 9:36 AM by 438877 RSS

    Display Grand Total on the same line.See output

    432292
      If I have a Table with the flwg data:
      TableA
      
      Col1   Col2  Col3   Amt
      1                   10
      2                   20
      3                   30
      4                   40
      How can I get the report output in the format below:
      ....            Amt    Total
      
                      10
                      20
                      30
                      40     100
      Is this possible?
        • 1. Re: Display Grand Total on the same line.See output
          451021
          SQL> select sum(sal) , deptno from emp group by rollup(deptno);

          SUM(SAL) DEPTNO
          --------- ---------
          8750 10
          10875 20
          9400 30
          29025

          we can get the sum in the same line by using the rollup. But could not get in the requested format. In case if you are using reports, u can play with a summary column.
          • 2. Re: Display Grand Total on the same line.See output
            432292
            yes,used summary columns,but output is
                         Amt
                         ----
                        10 
                        20
                        30
                        40
            
            Total   100
            The total (is a summary column) but is not on the same line
            • 3. Re: Display Grand Total on the same line.See output
              438877
              Simple example:
              SQL> select ename, sal, case when lv=ename then total else null end total
                2  from (
                3  select ename, sal, sum(sal) over(order by ename) Total, last_value(ename)
                4  over (order by ename range between current row and unbounded following) lv from emp
                5  )
                6  /

              ENAME             SAL      TOTAL
              ---------- ---------- ----------
              ADAMS            1100
              ALLEN            1800
              BLAKE            2850
              CLARK            2450
              FORD             3000
              JAMES            1120
              JONES            2975
              KING             5000
              MARTIN           1250
              MILLER           1300
              SCOTT            3000
              SMITH            1000
              TURNER           1500
              WARD             1450      29795

              14 rows selected.
              Rgds.
              • 4. Re: Display Grand Total on the same line.See output
                451021
                is this what you wanted,

                select empno, ename, deptno, sal, null "Total" from emp
                union
                select null, null, null, null, sum(sal) from emp
                /

                EMPNO ENAME DEPTNO SAL Total
                ----- ---------- --------- --------- ---------
                7369 SMITH 20 800
                7499 ALLEN 30 1600
                7521 WARD 30 1250
                7566 JONES 20 2975
                7654 MARTIN 30 1250
                7698 BLAKE 30 2850
                7782 CLARK 10 2450
                7788 SCOTT 20 3000
                7839 KING 10 5000
                7844 TURNER 30 1500
                7876 ADAMS 20 1100
                7900 JAMES 30 950
                7902 FORD 20 3000
                7934 MILLER 10 1300
                29025
                • 5. Re: Display Grand Total on the same line.See output
                  Laurent Schneider
                  without subquery
                  SQL> select ename, sal,
                  decode(empno, max(empno) over (),sum(sal) over()) total 
                  from emp order by empno;
                  ENAME             SAL      TOTAL
                  ---------- ---------- ----------
                  SMITH             800
                  ALLEN            1600
                  WARD             1250
                  JONES            2975
                  MARTIN           1250
                  BLAKE            2850
                  CLARK            2450
                  SCOTT            3000
                  KING             5000
                  TURNER           1500
                  ADAMS            1100
                  JAMES             950
                  FORD             3000
                  MILLER           1300      29025
                  • 6. Re: Display Grand Total on the same line.See output
                    432292
                    Hi,
                    Thanks a lot for your SQL QUERY

                    This is the table:
                    CONTRACT_TAG        FEE  FEE_MONTH      
                    ------------ ---------- ---------- 
                               1      21.36      21.36
                               1      21.36      42.72
                               1      24.42      67.14
                               1      18.27      18.27
                               1      21.83       40.1
                               1      20.37      60.47
                               1      50.07     110.54
                               2        .84        .84
                               2       1.12       1.12
                               2       1.12       2.24
                               2       2.52       4.76
                               2       2.52       7.28
                               4      18.96      18.96     
                               4      18.87      37.83     
                               4         19      56.83     
                    And this is the output
                    CONTRACT_TAG        FEE  FEE_MONTH      TOTAL
                    ------------ ---------- ---------- ----------
                               1      21.36      21.36
                               1      21.36      42.72
                               1      24.42      67.14
                               1      18.27      18.27
                               1      21.83       40.1
                               1      20.37      60.47
                               1      50.07     110.54      360.60
                               2        .84        .84
                               2       1.12       1.12
                               2       1.12       2.24
                               2       2.52       4.76
                               2       2.52       7.28     16.24
                               4      18.96      18.96     
                               4      18.87      37.83     
                               4         19      56.83     113.62
                    • 7. Re: Display Grand Total on the same line.See output
                      432292
                      please help with the above output using DECODE
                      I tried using this :
                      SELECT c.cont_tag contract_tag
                           , ch.fee
                           , ch.pool_id
                           , ( SELECT SUM(ca.fee)
                               FROM stock_borrow_contract_accrued ca
                             ) as "fee_month"
                            ,decode(ch.cont_tag,max(ch.cont_tag) over(),sum(ch.fee_month) over()) total
                      FROM
                           contract_type ctyp,
                           contract_accued ch
                      not correct result
                      • 8. Re: Display Grand Total on the same line.See output
                        27876
                        Something like this (adding to Laurent example above) is what you are looking for?
                        SQL> SELECT empno,
                          2         deptno,
                          3         ename,
                          4         sal,
                          5         DECODE(empno,
                          6                last_value(empno)
                          7                over(PARTITION BY deptno ORDER BY empno ROWS BETWEEN UNBOUNDED
                          8                     PRECEDING AND UNBOUNDED FOLLOWING),
                          9                SUM(sal) over(PARTITION BY deptno)) total
                        10  FROM   emp
                        11  ORDER  BY deptno,
                        12            empno;

                             EMPNO     DEPTNO ENAME             SAL      TOTAL
                        ---------- ---------- ---------- ---------- ----------
                              7782         10 CLARK            2450
                              7839         10 KING             5000
                              7934         10 MILLER           1300       8750
                              7369         20 SMITH             800
                              7566         20 JONES            2975
                              7788         20 SCOTT            3000
                              7876         20 ADAMS            1100
                              7902         20 FORD             3000      10875
                              7499         30 ALLEN            1600
                              7521         30 WARD             1250
                              7654         30 MARTIN           1250
                              7698         30 BLAKE            2850
                              7844         30 TURNER           1500
                              7900         30 JAMES             950       9400

                        14 rows selected.

                        SQL>
                        • 9. Re: Display Grand Total on the same line.See output
                          Laurent Schneider
                          maybe something like
                          select c.cont_tag contract_tag
                               , ch.fee
                               , ch.pool_id
                               , SUM(ca.fee)
                               decode(ch.fee||'-'||ch.pool_id, max(ch.fee||'-'
                                   ||ch.pool_id) over (partition by c.cont_tag),
                                   sum(sum(ca.fee)) over (partition by c.cont_tag)) total
                          from contract_type ctyp,contract_accued ch
                          where 1=1  -- are there no join?
                          group by c.cont_tag contract_tag
                               , ch.fee
                               , ch.pool_id
                          ;
                          similar with emp
                          select job, deptno, sum(sal),
                          decode(deptno, max(deptno) over (partition by job),
                          sum(sum(sal)) over(partition by job)) total
                          from emp
                          group by job,deptno
                          order by job,deptno
                          /
                          
                          JOB           DEPTNO   SUM(SAL)      TOTAL
                          --------- ---------- ---------- ----------
                          ANALYST           20       6000       6000
                          CLERK             10       1300
                          CLERK             20       1900
                          CLERK             30        950       4150
                          MANAGER           10       2450
                          MANAGER           20       2975
                          MANAGER           30       2850       8275
                          PRESIDENT         10       5000       5000
                          SALESMAN          30       5600       5600
                          HTH

                          Message was edited by:
                          Laurent Schneider
                          sum(sum(ca.fee)) is surprising, is not it ;-)

                          Message was edited by:
                          Laurent Schneider
                          I forgot group by in first query
                          • 10. Re: Display Grand Total on the same line.See output
                            438877
                            Seems you are trying to make an incremental sum of fee and after that -
                            total sum of this incremental sums (if it's right interpretation of your question),
                            something like:
                            SQL> select deptno,ename,sal,fee,decode(ename,max(ename) over(partition by deptno),
                              2  sum(fee) over(partition by deptno),null) totals
                              3  from (
                              4  select deptno,
                              5  ename,
                              6  sal,
                              7  sum(sal) over(partition by deptno order by ename) fee
                              8  from emp
                              9  )
                            10  /

                                DEPTNO ENAME             SAL        FEE     TOTALS
                            ---------- ---------- ---------- ---------- ----------
                                    10 CLARK            2450       2450
                                    10 KING             5000       7450
                                    10 MILLER           1300       8750      18650
                                    20 ADAMS            1100       1100
                                    20 FORD             3000       4100
                                    20 JONES            2975       7075
                                    20 SCOTT            3000      10075
                                    20 SMITH            1000      11075      33425
                                    30 ALLEN            1800       1800
                                    30 BLAKE            2850       4650
                                    30 JAMES            1120       5770
                                    30 MARTIN           1250       7020
                                    30 TURNER           1500       8520
                                    30 WARD             1450       9970      37730

                            14 rows selected.
                            ?

                            Rgds.
                            • 11. Re: Display Grand Total on the same line.See output
                              432292
                              Hi,
                              Thanks a lot for sending your responses.

                              But I am unable to get the output as I want it.

                              The 'sum of fees' is computed for a join.

                              My code being used for this is:
                              SELECT  c.cont_tag
                                     ,ch.fee
                                     ,ch.pool_id
                                     ,( SELECT SUM(ca.fee)
                                        FROM stock_borrow_contract_accrued ca
                                             WHERE ca.cont_tag = ch.cont_tag
                                             AND ca.company_id = ch.company_id
                                             AND ca.eff_date <= ch.eff_date
                                             AND to_char(ca.eff_date, 'MM') = to_char(ch.eff_date, 'MM')
                                     )fee_month
                                     
                              FROM
                                   contract_type ctyp
                                  ,business_organisation borg
                                  ,business_organisation borgcpty
                                  ,currency curr
                                  ,stock_borrow_contract_accrued ch
                                  ,contract c
                                 WHERE
                                      ctyp.cont_type_code = c.cont_type_code
                                  AND c.cmpy_num = borg.borg_num
                                  AND c.cpty_borg_num  = borgcpty.borg_num
                                  AND c.cmpy_num = p.company_id
                                  AND c.cpty_borg_num = p.counterparty_id
                                  AND c.cont_curr_code = curr.curr_code
                                  AND ch.cont_tag = c.cont_tag
                                  AND ch.pool_id = p.id
                                 
                                  OUTPUT:
                                  -------------
                              
                                    CONT_TAG        FEE    POOL_ID  FEE_MONTH
                              ---------- ---------- ---------- ----------
                                       1      21.36          1      21.36
                                       1      21.36          1      42.72
                                       1      21.83          1       40.1
                                       1      18.27          1      18.27
                                       1      24.42          1      67.14
                                       1      20.37          1      60.47
                                       1      50.07          1     110.54
                                       2        .84          1        .84
                                       2       1.12          1       1.12
                                       2       1.12          1       2.24
                                       2       2.52          1       4.76
                                       2       2.52          1       7.28
                                       4      18.96          1      18.96
                                       4      18.87          1      37.83
                                       4         19          1      56.83
                              Now what I want is this
                              ---------------------------------
                                CONT_TAG        FEE    POOL_ID  FEE_MONTH       total
                              ---------- ---------- ---------- ----------
                                       1      21.36          1      21.36
                                       1      21.36          1      42.72
                                       1      21.83          1       40.1
                                       1      18.27          1      18.27
                                       1      24.42          1      67.14
                                       1      20.37          1      60.47
                                       1      50.07          1     110.54      360.60
                                       2        .84          1        .84
                                       2       1.12          1       1.12
                                       2       1.12          1       2.24
                                       2       2.52          1       4.76
                                       2       2.52          1       7.28        16.24
                                       4      18.96          1      18.96
                                       4      18.87          1      37.83
                                       4         19          1      56.83          113.62
                              I tried using the over() and decode() but cant get the output of the sums
                              of 360.60 16.24 and 113.62

                              I guess the reason is that becos its a join and each record being displayed
                              sums up the fees.

                              Is it possible to get the outputs as I want it?
                              • 12. Re: Display Grand Total on the same line.See output
                                438877
                                I would think of the query reconstruction to get rid of the subquery. Nevertheless:
                                SQL> select ename, job,
                                  2  (select sum(sal) from emp e1 where e1.deptno = e.deptno) part_sum,
                                  3  decode(max(ename) over(partition by job), ename,
                                  4  sum((select sum(sal) from emp e1 where e1.deptno = e.deptno))
                                  5  over(partition by job order by ename),null) totals
                                  6  from emp e
                                  7  /

                                ENAME      JOB         PART_SUM     TOTALS
                                ---------- --------- ---------- ----------
                                FORD       ANALYST        11075
                                SCOTT      ANALYST        11075      22150
                                ADAMS      CLERK          11075
                                JAMES      CLERK           9970
                                MILLER     CLERK           8750
                                SMITH      CLERK          11075      40870
                                BLAKE      MANAGER         9970
                                CLARK      MANAGER         8750
                                JONES      MANAGER        11075      29795
                                KING       PRESIDENT       8750       8750
                                ALLEN      SALESMAN        9970
                                MARTIN     SALESMAN        9970
                                TURNER     SALESMAN        9970
                                WARD       SALESMAN        9970      39880

                                14 rows selected.
                                Rgds.
                                • 13. Re: Display Grand Total on the same line.See output
                                  438877
                                  the same:
                                  SQL> select ename, job, part_sum,
                                    2  decode(max(ename) over(partition by job), ename,
                                    3  sum(part_sum) over(partition by job order by ename),null) totals
                                    4  from (
                                    5  select ename, job,
                                    6  (select sum(sal) from emp e1 where e1.deptno = e.deptno) part_sum
                                    7  from emp e
                                    8  )
                                    9  /

                                  ENAME      JOB         PART_SUM     TOTALS
                                  ---------- --------- ---------- ----------
                                  FORD       ANALYST        11075
                                  SCOTT      ANALYST        11075      22150
                                  ADAMS      CLERK          11075
                                  JAMES      CLERK           9970
                                  MILLER     CLERK           8750
                                  SMITH      CLERK          11075      40870
                                  BLAKE      MANAGER         9970
                                  CLARK      MANAGER         8750
                                  JONES      MANAGER        11075      29795
                                  KING       PRESIDENT       8750       8750
                                  ALLEN      SALESMAN        9970
                                  MARTIN     SALESMAN        9970
                                  TURNER     SALESMAN        9970
                                  WARD       SALESMAN        9970      39880

                                  14 rows selected.
                                  Rgds.