Home » SQL & PL/SQL » SQL & PL/SQL » Adding row values (Windows Xp, Oracle 9i)
Adding row values [message #328761] Sun, 22 June 2008 12:30 Go to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Hey if i have a column say employee salary and i want to add all the values of the salary and create a row at the bottom saying total. How to do so?

Do we have a function like avg. I tried add(columnname) but it gives error.

Re: Adding row values [message #328762 is a reply to message #328761] Sun, 22 June 2008 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

The Fine SQL Reference Manual can be found at http://tahiti.oracle.com.

It is past time for you to RTFM!
Re: Adding row values [message #328763 is a reply to message #328761] Sun, 22 June 2008 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*PlusŪ User's Guide and Reference, see BREAK and COMPUTE commands.

Regards
Michel
Re: Adding row values [message #328765 is a reply to message #328762] Sun, 22 June 2008 12:55 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Hey ana i have read the posting guidelines before..yes i should have referred to the documents before posting.

Apologies
Re: Adding row values [message #328768 is a reply to message #328765] Sun, 22 June 2008 13:20 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Hey,


SQL> break on ename dup
SQL> COMPUTE SUM OF SAL ON DEPTNO
SQL> select ename, sal from emp;

ENAME             SAL
---------- ----------
SMITH             800
********** ----------
sum               800
ALLEN            1600
********** ----------
sum              1600
WARD             1250
********** ----------
sum              1250
JONES            2975
********** ----------

ENAME             SAL
---------- ----------
sum              2975
MARTIN           1250
********** ----------
sum              1250
BLAKE            2850
********** ----------
sum              2850
CLARK            2450
********** ----------
sum              2450
SCOTT            3000

ENAME             SAL
---------- ----------
********** ----------
sum              3000
KING             5000
********** ----------
sum              5000
TURNER           1500
********** ----------
sum              1500
ADAMS            1100
********** ----------
sum              1100

ENAME             SAL
---------- ----------
JAMES             950
********** ----------
sum               950
FORD             3000
********** ----------
sum              3000
MILLER           1300
********** ----------
sum              1300

14 rows selected.


I am not getting the required output with this.
Re: Adding row values [message #328770 is a reply to message #328768] Sun, 22 June 2008 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SQL> COMPUTE SUM OF SAL ON DEPTNO
SQL> select ename, sal from emp;


You don't select deptno, so it can't give the sum by deptno.

Regards
Michel

Re: Adding row values [message #328771 is a reply to message #328770] Sun, 22 June 2008 13:34 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
hey


SQL> break on deptno nodup
SQL> compute sum of sal on deptno
SQL> select deptno , sal from emp;

    DEPTNO        SAL
---------- ----------
        20        800
********** ----------
sum               800
        30       1600
                 1250
********** ----------
sum              2850
        20       2975
********** ----------
sum              2975
        30       1250

    DEPTNO        SAL
---------- ----------
        30       2850
********** ----------
sum              4100
        10       2450
********** ----------
sum              2450
        20       3000
********** ----------
sum              3000
        10       5000
********** ----------

    DEPTNO        SAL
---------- ----------
sum              5000
        30       1500
********** ----------
sum              1500
        20       1100
********** ----------
sum              1100
        30        950
********** ----------
sum               950
        20       3000

    DEPTNO        SAL
---------- ----------
********** ----------
sum              3000
        10       1300
********** ----------
sum              1300

14 rows selected.


I want to create just one more row below the sal column saying total, sum or anything. i do not want the same value in the middle as it is appearing for each dept, i wanted for overall. how to do so?
Re: Adding row values [message #328772 is a reply to message #328771] Sun, 22 June 2008 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have a sum each time the value changes, if you want a value per department then you have to modify the query to get the output department per department.

If you just want the sum per department and not the value per employee, then you have to read SQL Reference about GROUP BY and aggregate functions.

Regards
Michel
Re: Adding row values [message #328773 is a reply to message #328772] Sun, 22 June 2008 14:05 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
hey,

No no i do not want the value per dept or per ename i just want to create another cell at the end of the sal column which says total salary

[code]
SQL> select sal from emp;

SAL
----------
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100

SAL
----------
950
3000
1300

Total 29025


[\code]

something like i have copy pasted above
Re: Adding row values [message #328774 is a reply to message #328761] Sun, 22 June 2008 14:08 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>i just want to create another cell
The use Excel or similar spreadsheet.
Re: Adding row values [message #328776 is a reply to message #328761] Sun, 22 June 2008 14:22 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> i just want to create another cell at the end of the sal column which says total salary

If "cell" is another term for an output column, you may use one of analytic functions.

If you want to achieve the posted output (without sums on DEPTNO), why do you BREAK and COMPUTE on DEPTNO? You shall do it on REPORT.

[Edit: Added last paragraph, as I did not notice, that the is probably required output]

[Updated on: Sun, 22 June 2008 14:32]

Report message to a moderator

Re: Adding row values [message #328777 is a reply to message #328776] Sun, 22 June 2008 14:26 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
hey but it gives me cumulative value i do not want that.

I want to create an output as



 SAL
----------
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100

SAL
----------
950
3000
1300

Total 29025
Re: Adding row values [message #328779 is a reply to message #328773] Sun, 22 June 2008 14:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So reread the link I posted, you can do it with BREAK and COMPUTE.
If you don't want per deptno then don't break on deptno, if you don't want to compute per deptno, then don't say you want this. SQL*Plus is just doing what you're asking it to do.

Regards
Michel
Re: Adding row values [message #328781 is a reply to message #328779] Sun, 22 June 2008 14:40 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
alright,

So i do not want to break on anything just compute the sal. So i will not specify
break on colname

right?
Re: Adding row values [message #328782 is a reply to message #328781] Sun, 22 June 2008 14:45 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
sdhanuka wrote on Sun, 22 June 2008 12:40
alright,

So i do not want to break on anything just compute the sal. So i will not specify
break on colname

right?


which gets you answer in the least amount of time?
Post question here & wait for answer?
or
execute the SQL & see the results?
Re: Adding row values [message #328783 is a reply to message #328782] Sun, 22 June 2008 14:48 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
execute the script and see the result Smile
Re: Adding row values [message #328828 is a reply to message #328761] Mon, 23 June 2008 00:13 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0
With the Partitioning, OLAP and Oracle Data M
JServer Release 9.2.0.1.0 - Production

SQL> select sal from emp;

       SAL
----------
       800
      1600
      1250
      2975
      1250
      2850
      2450
      3000
      5000
      1500
      1100

       SAL
----------
       950
      3000
      1300

14 rows selected.

SQL> break on dummy;
SQL> compute sum of sal on dummy;
SQL> select null dummy,sal from emp;

D        SAL
- ----------
         800
        1600
        1250
        2975
        1250
        2850
        2450
        3000
        5000
        1500
        1100

D        SAL
- ----------
         950
        3000
        1300
* ----------
s      29025

14 rows selected.

SQL> column dummy noprint;
SQL> /

       SAL
----------
       800
      1600
      1250
      2975
      1250
      2850
      2450
      3000
      5000
      1500
      1100

       SAL
----------
       950
      3000
      1300
----------
     29025

14 rows selected.

SQL> 



regards,
Re: Adding row values [message #328839 is a reply to message #328828] Mon, 23 June 2008 00:56 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wrong way to do it.
SQL*Plus provides the feature.
Read what I posted.

Regards
Michel
Previous Topic: index on coulmns
Next Topic: Whats wrong with the following code?
Goto Forum:
  


Current Time: Sun Dec 04 20:28:07 CST 2016

Total time taken to generate the page: 0.05050 seconds