Re: Question for SQL Plus

A copy of this was sent to cheers_up_at_hotmail.com (if that email address didn't require changing) On Sat, 29 May 1999 17:54:29 GMT, you wrote:

>Hi,
>
>Anyone could please help me to solve the following
>problem. Thanks in advance!
>
>Assume, I have following records in a emp table:
>
> emp_id
> ------
> 2
> 4
> 1
> 5
> 6
>How do you write a SQL query to have the following result?
>
> emp_id
> ------
> 2
> 4
> 1
> 5
> 6
> -----
> Total 18
>
>

well -- in sqlplus you can

SQL> break on report
SQL> compute sum of empno on report
SQL> select empno from emp;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

    108172

14 rows selected.

but thats not really a 'query'. In Oracle8i, release 8.1 using some of the new grouping functions you can do this in a query without a report tool:

  1 select decode( grouping(empno), 1, 'Total', null ), sum(empno)   2 from emp
  3* group by rollup(empno)
SQL> / DECOD SUM(EMPNO)
----- ----------

            7369
            7499
            7521
            7566
            7654
            7698
            7782
            7788
            7839
            7844
            7876
            7900
            7902
            7934
Total     108172

15 rows selected.

>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat May 29 1999 - 14:55:01 CDT