Home » SQL & PL/SQL » SQL & PL/SQL » give the sql query for the below scenario
give the sql query for the below scenario [message #230308] Wed, 11 April 2007 08:53 Go to next message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

hi gurus,
give the sql query for the below scenario,
i have a table like below

ename salary
A 100
B 200
c 300
d 400

the output

ename salary
A 100
B 300 (100+200)
C 600 (100+200+300)
D 1000 (100+200+300+400)
Re: give the sql query for the below scenario [message #230310 is a reply to message #230308] Wed, 11 April 2007 09:06 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
No, try it yourself first and show us your code.
Re: give the sql query for the below scenario [message #230315 is a reply to message #230308] Wed, 11 April 2007 09:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a clue: sum over.

Regards
Michel

Re: give the sql query for the below scenario [message #230462 is a reply to message #230308] Thu, 12 April 2007 01:32 Go to previous messageGo to next message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

Hi,
I tried with below query i know that it will work with cursors,
Is there any chance with out using pl/sql?
Just tell yes/no.


select c1,c2,decode(mod(rownum,1),0,count(c2),null) result from x1;

And the table is

x1
c1 c2

a 100
b 200
c 300

bye....

Re: give the sql query for the below scenario [message #230472 is a reply to message #230462] Thu, 12 April 2007 01:42 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

iitraghu wrote on Thu, 12 April 2007 01:32
Just tell yes/no.


yes it is possible.

check the sum over as pointed by Michel Cadot

regards,
shanth.
Re: give the sql query for the below scenario [message #230493 is a reply to message #230472] Thu, 12 April 2007 02:21 Go to previous messageGo to next message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

Hi,
Shanth,
Give a clue,
I think i have to use the break on row compute commands yes/no?


bye,
raghu
Re: give the sql query for the below scenario [message #230496 is a reply to message #230493] Thu, 12 April 2007 02:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
BREAK and COMPUTE are SQL*plus commands. Unless you're running this query only from SQL*Plus, you're looking for an analytic function that SUMs the values.

MHE
Re: give the sql query for the below scenario [message #230500 is a reply to message #230496] Thu, 12 April 2007 02:32 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

the clue is sum over

regrads
shanth.




[Updated on: Thu, 12 April 2007 02:35]

Report message to a moderator

Re: give the sql query for the below scenario [message #230503 is a reply to message #230500] Thu, 12 April 2007 02:46 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
asume that emp table in scoot shema.

select sum(sal) over (order by sal asc ) as sal from emp;

thanks
srinivas
Re: give the sql query for the below scenario [message #230510 is a reply to message #230503] Thu, 12 April 2007 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now that you did the OP exercise, what did he learn?

Regards
Michel
Re: give the sql query for the below scenario [message #230529 is a reply to message #230503] Thu, 12 April 2007 03:11 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi all,
iam using this query.

select sal, sum(sal) over ( order by sal asc ) as sal1 from emp;

iam getting those result.
SAL	SAL1

800	800
950	1750
1100	2850
1250	5350
1250	5350
1300	6650
1500	8150
1600	9750
2450	12200
2850	15050
2975	18025
3000	24025
3000	24025
5000	29025

i want this way
SAL	SAL1

800	800
950	1750
1100	2850
1250	5350
1250	5350+1250
1300	5350+1250+1300
1500	5350+1250+1300+1500
1600	5350+1250+1300+1500+1600
2450	5350+1250+1300+1500+1600+2450
2850	5350+1250+1300+1500+1600+2450+2850
2975	5350+1250+1300+1500+1600+2450+2850+2975
3000    5350+1250+1300+1500+1600+2450+2850+2975+3000
3000	5350+1250+1300+1500+1600+2450+2850+2975+3000+3000
5000	5350+1250+1300+1500+1600+2450+2850+2975+3000+3000+5000



thanks
srinivas


I don't Know what's wrong with you...why you forget to add CODE tags.Smile

Taj

[Updated on: Thu, 12 April 2007 03:18]

Report message to a moderator

Re: give the sql query for the below scenario [message #230532 is a reply to message #230529] Thu, 12 April 2007 03:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do only 2 rows get the +?
Why don't the last couple of rows get a summed sal?
Re: give the sql query for the below scenario [message #230534 is a reply to message #230532] Thu, 12 April 2007 03:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Search for concat_all/stragg.

MHE
Re: give the sql query for the below scenario [message #230559 is a reply to message #230534] Thu, 12 April 2007 03:53 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
iam using 9i,

is it work(concat_all)

select d.dname,NVL(concat_all(concat_expr(e.ename,',')),'None') enames
          from emp e,dept d
          where e.deptno(+) = d.deptno
          group by d.dname


iam getting error.
Thanks,
srinivas

[Updated on: Thu, 12 April 2007 03:55]

Report message to a moderator

Re: give the sql query for the below scenario [message #230563 is a reply to message #230559] Thu, 12 April 2007 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
iam getting error

is not a valid Oracle error.

Regards
Michel
Re: give the sql query for the below scenario [message #230566 is a reply to message #230559] Thu, 12 April 2007 04:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
pavuluri wrote on Thu, 12 April 2007 10:53
iam using 9i,
What version: 9iR1, 9iR2. Here's how to find out:
SQL> select banner
  2  from   v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL/SQL Release 9.2.0.3.0 - Production
CORE    9.2.0.3.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production


pavuluri wrote on Thu, 12 April 2007 10:53
is it work(concat_all)
Does it work? We don't have it installed right here but I've seen it run on a production database so I'm pretty confident it will work, provided that you have installed it correctly.

select d.dname,NVL(concat_all(concat_expr(e.ename,',')),'None') enames
          from emp e,dept d
          where e.deptno(+) = d.deptno
          group by d.dname


pavuluri wrote on Thu, 12 April 2007 10:53
iam getting error.
You are getting WHAT error? Be precise.

MHE
Re: give the sql query for the below scenario [message #230578 is a reply to message #230566] Thu, 12 April 2007 04:14 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
ORA-00904: "CONCAT_ALL": invalid identifier



SQL*Plus: Release 9.2.0.1.0 - Production on Thu Apr 12 14:52:14 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.



thanks

srinivas

[Updated on: Thu, 12 April 2007 04:23]

Report message to a moderator

Re: give the sql query for the below scenario [message #230583 is a reply to message #230578] Thu, 12 April 2007 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00904: "CONCAT_ALL": invalid identifier

I wonder what this means.

Regards
Michel
Re: give the sql query for the below scenario [message #230585 is a reply to message #230583] Thu, 12 April 2007 04:31 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

please find the screen shot michel
Re: give the sql query for the below scenario [message #230595 is a reply to message #230585] Thu, 12 April 2007 04:45 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
pavuluri,
you do know that this is a aggregate function you have to create yourself?

Link

Edit: and let there be no mistake about the origin of concat_all.

MHE

[Updated on: Thu, 12 April 2007 04:46]

Report message to a moderator

Previous Topic: convert DataTypes
Next Topic: how can i get back my emp table 14 rows
Goto Forum:
  


Current Time: Thu Dec 08 16:14:59 CST 2016

Total time taken to generate the page: 0.07909 seconds