OraFAQ Forum: SQL & PL/SQL » show sum

Home » SQL & PL/SQL » SQL & PL/SQL » show sum (Oracle,9.2.0.8.0,XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

show sum [message #310985]

Thu, 03 April 2008 03:01

gozuhair
Messages: 206
Registered: January 2008

Senior Member

Dear All

please help.

Create table abc
(sal number(5),empno varchar2(5));

insert into abc
(100,1)
insert into abc
(100,2)
insert into abc
(1000,3)

commit;

I want the following output using select statement.

sal empno

100 1
100 2
1000 3
1200

regards
Zuhair

[Updated on: Thu, 03 April 2008 03:06] by Moderator

Report message to a moderator

Re: show sum [message #310987 is a reply to message #310985]

Thu, 03 April 2008 03:07

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What about formatting your posts and giving syntaxically correct statements?

Where does come this 1200?

Regards
Michel

Report message to a moderator

Re: show sum [message #310988 is a reply to message #310985]

Thu, 03 April 2008 03:07

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

Post what you tried.You could use the SQLPLUS COMPUTE SUM.

[edit:corrected my post]

regards,

[Updated on: Thu, 03 April 2008 03:11]

Report message to a moderator

Re: show sum [message #310992 is a reply to message #310987]

Thu, 03 April 2008 03:26

gozuhair
Messages: 206
Registered: January 2008

Senior Member

Create table abc (
sal number(5),
empNo varchar2(5));

insert into abc
values (100,
1);

insert into abc
values (100,
2);

insert into abc
values (1000,
3);

commit;

Need sum of 100+100+1000 at the end of rows selected like this

sal

100
100
1000
1200

Report message to a moderator

Re: show sum [message #310995 is a reply to message #310985]

Thu, 03 April 2008 03:30

flyboy
Messages: 1903
Registered: November 2006

Senior Member

If you want to do in single SQL query, check the ROLLUP extension of GROUP BY clause. Example may be found eg. in the description of the GROUPING function.

Report message to a moderator

Re: show sum [message #310996 is a reply to message #310995]

Thu, 03 April 2008 03:38

gozuhair
Messages: 206
Registered: January 2008

Senior Member

by using rollup function sum of salary show in each row but i want sum of all rows on the last line not each record.

Report message to a moderator

Re: show sum [message #310998 is a reply to message #310996]

Thu, 03 April 2008 03:44

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Try again, you missused it.
And please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: show sum [message #311007 is a reply to message #310985]

Thu, 03 April 2008 04:01

gozuhair
Messages: 206
Registered: January 2008

Senior Member

I am using the following query

SELECT empNo,
sal
FROM abc
GROUP BY ROLLUP(sal,empNo);

The above query show the following output.

EMPNO SAL
----- ---------
100 1
1
100 2
2
1000 3
3

Desired Output.

EMPNO SAL
----- ---------
100 1
100 2
1000 3
1200

(means 1200 equal to 100+100+1000)

Report message to a moderator

Re: show sum [message #311009 is a reply to message #311007]

Thu, 03 April 2008 04:10

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

As I said you badly used the clause.
ANd you still don't format your post.

Regards
Michel

Report message to a moderator

Re: show sum [message #311070 is a reply to message #310985]

Thu, 03 April 2008 05:58

gozuhair
Messages: 206
Registered: January 2008

Senior Member

I am using the following query

SELECT empNo,
sal
FROM abc
GROUP BY ROLLUP(sal,empNo);

The above query show the following output.

EMPNO SAL
----- ---------
100   1
      1
100   2
      2
1000  3
      3

Desired Output.

EMPNO SAL
----- ---------
100  1
100  2
1000 3
1200

(means 1200 equal to 100+100+1000)

Report message to a moderator

Re: show sum [message #311071 is a reply to message #311070]

Thu, 03 April 2008 06:02

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Have a look at the Grouping function. You can use this to restrict the rows that you don't want to see.

Report message to a moderator

Re: show sum [message #311076 is a reply to message #310985]

Thu, 03 April 2008 06:31

gozuhair
Messages: 206
Registered: January 2008

Senior Member

can you please make a query of it,if you could make it then i will very grateful to you.

Report message to a moderator

Re: show sum [message #311077 is a reply to message #311070]

Thu, 03 April 2008 06:34

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

Quote:

SELECT empNo,
sal
FROM abc
GROUP BY ROLLUP(sal,empNo);

This query is not correct. I will give you the query which won't give you the correct result but I will you let you figure it out how to do it. If you read the documentation you will understand it.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/aggreg.htm#i1007462

Try this query but as I said earlier it will not give you the result in your expected format.

select empno, sum(sal) from abc
group by rollup(empno, sal);

Happy learning sql.

Regards

Raj

[Updated on: Thu, 03 April 2008 06:35]

Report message to a moderator

Re: show sum [message #311079 is a reply to message #311076]

Thu, 03 April 2008 06:42

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

try this out:-

SELECT empno,SUM(sal) FROM abc GROUP BY rollup(empno)

Report message to a moderator

Re: show sum [message #311096 is a reply to message #310985]

Thu, 03 April 2008 07:04

gozuhair
Messages: 206
Registered: January 2008

Senior Member

Thanks you guys for answering but unfortunately all queries are not giving the right result.

Report message to a moderator

Re: show sum [message #311105 is a reply to message #311096]

Thu, 03 April 2008 07:20

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Actually it does it for me.

Regards
Michel

Report message to a moderator

Previous Topic:	Getting Columns Selected Via A Query Dynamically
Next Topic:	PL/SQL Exception

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 23:41:44 CDT 2024