Home » SQL & PL/SQL » SQL & PL/SQL » Needs sum of multiple columns in a table
Needs sum of multiple columns in a table [message #349698] Mon, 22 September 2008 15:01 Go to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
Hi all,
Say, I have a table as follows

COL1 COL2 COL3
------ ------ -----

100 100 200
100 200 300
100 300 400

I need to take sum of all these columns. The final result should be 1800

PS: The above data is just sample and can vary
Sorry for the formatting.

Thanks in Advance
Re: Needs sum of multiple columns in a table [message #349699 is a reply to message #349698] Mon, 22 September 2008 15:05 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
rak007 wrote on Mon, 22 September 2008 16:01

Sorry for the formatting.



Then do something about it like reading the sticky on how to format it properly. you should know this after 50 posts.

A simple sum of the columns plus the sum of that will give you the answer.
Re: Needs sum of multiple columns in a table [message #349700 is a reply to message #349698] Mon, 22 September 2008 15:06 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@rak007,

Use SUM function on each column (probably in an inline view) and sum up the results you get in your main SELECT clause.

Regards,
Jo
Re: Needs sum of multiple columns in a table [message #349701 is a reply to message #349699] Mon, 22 September 2008 15:07 Go to previous messageGo to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
@joy_division

Can you please list down the query



@joicejohn

I have multiple columns in the table (COL8 to COL63) and need sum of all these. I think i will have to use loops but am not getting exactly how to use.

[Updated on: Mon, 22 September 2008 15:08]

Report message to a moderator

Re: Needs sum of multiple columns in a table [message #349705 is a reply to message #349701] Mon, 22 September 2008 15:21 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@rak007,
rak007 wrote on Tue, 23 September 2008 01:37

I have multiple columns in the table (COL8 to COL63) and need sum of all these. I think i will have to use loops but am not getting exactly how to use.

I don't understand why you have to use loops here. To be frank I am not able to understand your requirement properly. If you know the column names and the number of columns you have to sum then my answer remains the same.

rak007 wrote on Tue, 23 September 2008 01:37

Can you please list down the query



Not allowed to do that bro...
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Post the queries you have tried with a test case.
Read the Posting Guidelines.

Regards,
Jo
Re: Needs sum of multiple columns in a table [message #349706 is a reply to message #349701] Mon, 22 September 2008 15:24 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
i will have to use loops

No; you'll need 56 SUM functions and 55 "+" signs.
Re: Needs sum of multiple columns in a table [message #349738 is a reply to message #349698] Mon, 22 September 2008 22:42 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hope, OP might have got the solution by this time Smile

Thumbs Up
Rajuvan

[Updated on: Mon, 22 September 2008 22:42]

Report message to a moderator

Re: Needs sum of multiple columns in a table [message #349783 is a reply to message #349706] Tue, 23 September 2008 02:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
No; you'll need 56 SUM functions and 55 "+" signs.


I think you can do it with 1 SUM function and 55 + signs
Re: Needs sum of multiple columns in a table [message #349803 is a reply to message #349783] Tue, 23 September 2008 03:26 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right!

Additionally, if there's a NULL somewhere in the table, he'll need up to 56 NVL functions.
Re: Needs sum of multiple columns in a table [message #349862 is a reply to message #349698] Tue, 23 September 2008 06:07 Go to previous messageGo to next message
chakradhar.adhikari
Messages: 5
Registered: September 2008
Junior Member
DECLARE
v_sum NUMBER (10) := 0;
BEGIN
FOR c IN (SELECT *
FROM t)
LOOP
v_sum := v_sum + c.c1 + c.c2 + c.c3;
END LOOP;

DBMS_OUTPUT.put_line (v_sum);
END;
Re: Needs sum of multiple columns in a table [message #349865 is a reply to message #349862] Tue, 23 September 2008 06:16 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi chakradhar.adhikari ,

Welcome to the exciting world of Orafaq .
Also please read Posting Guidelines before the next posting .

Thumbs Up
Rajuvan.
Re: Needs sum of multiple columns in a table [message #349867 is a reply to message #349698] Tue, 23 September 2008 06:25 Go to previous messageGo to next message
chakradhar.adhikari
Messages: 5
Registered: September 2008
Junior Member


I create table like

create table t(c1 nimber(10),c2 number(10),c3 number(10));

insert some values

insert into t values(10,20,30);
insert into t values(10,20,30);
insert into t values(10,20,30);

SELECT SUM (a.c1 + a.c2 + a.c3)
FROM t a
WHERE EXISTS (SELECT 1
FROM t b
WHERE a.c1 = b.c1)

Result: 180

I hope it is useful.

[Updated on: Tue, 23 September 2008 06:35]

Report message to a moderator

Re: Needs sum of multiple columns in a table [message #349887 is a reply to message #349867] Tue, 23 September 2008 07:04 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not following the guidelines.

Regards
Michel
Previous Topic: to see the functions in sql *plus monitor
Next Topic: problem with stored procedure and asp page.
Goto Forum:
  


Current Time: Sat Dec 03 05:40:40 CST 2016

Total time taken to generate the page: 0.05983 seconds