Home » SQL & PL/SQL » SQL & PL/SQL » show sum (Oracle,9.2.0.8.0,XP)
show sum [message #310985] Thu, 03 April 2008 03:01 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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
Re: show sum [message #310988 is a reply to message #310985] Thu, 03 April 2008 03:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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



Re: show sum [message #310995 is a reply to message #310985] Thu, 03 April 2008 03:30 Go to previous messageGo to next message
flyboy
Messages: 1832
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.
Re: show sum [message #310996 is a reply to message #310995] Thu, 03 April 2008 03:38 Go to previous messageGo to next message
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.
Re: show sum [message #310998 is a reply to message #310996] Thu, 03 April 2008 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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
Re: show sum [message #311007 is a reply to message #310985] Thu, 03 April 2008 04:01 Go to previous messageGo to next message
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)

Re: show sum [message #311009 is a reply to message #311007] Thu, 03 April 2008 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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
Re: show sum [message #311070 is a reply to message #310985] Thu, 03 April 2008 05:58 Go to previous messageGo to next message
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)
Re: show sum [message #311071 is a reply to message #311070] Thu, 03 April 2008 06:02 Go to previous messageGo to next message
pablolee
Messages: 2836
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.
Re: show sum [message #311076 is a reply to message #310985] Thu, 03 April 2008 06:31 Go to previous messageGo to next message
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.
Re: show sum [message #311077 is a reply to message #311070] Thu, 03 April 2008 06:34 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
try this out:-

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

Re: show sum [message #311096 is a reply to message #310985] Thu, 03 April 2008 07:04 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks you guys for answering but unfortunately all queries are not giving the right result.
Re: show sum [message #311105 is a reply to message #311096] Thu, 03 April 2008 07:20 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually it does it for me.

Regards
Michel
Previous Topic: Getting Columns Selected Via A Query Dynamically
Next Topic: PL/SQL Exception
Goto Forum:
  


Current Time: Sat Dec 10 05:03:01 CST 2016

Total time taken to generate the page: 0.13837 seconds