OraFAQ Forum: SQL & PL/SQL » Sum(cost) basing on id entered (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Sum(cost) basing on id entered (merged)

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

Sum(cost) basing on id entered (merged) [message #567386]

Mon, 01 October 2012 00:39

xerox2012
Messages: 15
Registered: October 2012

Junior Member

my data :

id cost
aa_aa 100
ab_aa 100
bb_bb 100
ba_bb 100
ab_aa 100

I need to get my data as
if id like 'aa_%' then it should sum cost for ids for aa_aa and ab_aa and print only 1 row with putput as

id cost
aa_aa 300
If id is like 'ab_%' then it should sum up
ids only for ab_aa..
Output as:
id cost
ab_aa 200

Report message to a moderator

Re: Sum of costs [message #567387 is a reply to message #567386]

Mon, 01 October 2012 01:06

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

you are excepting this one...

select * from test where value like'%aa%'

Report message to a moderator

Re: Sum of costs [message #567388 is a reply to message #567387]

Mon, 01 October 2012 01:08

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Try like..

select sum(value) from test where value like'%aa%'

Report message to a moderator

Re: Sum of costs [message #567389 is a reply to message #567388]

Mon, 01 October 2012 01:10

xerox2012
Messages: 15
Registered: October 2012

Junior Member

No.. that will not work, basing on the user entered, it should sum up and give the values... that alone will not work...you cant filter as aa_aa or ab_aa

Report message to a moderator

Re: Sum of costs [message #567391 is a reply to message #567386]

Mon, 01 October 2012 01:25

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

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Report message to a moderator

Sum(cost) basing on id entered [message #567394 is a reply to message #567386]

Mon, 01 October 2012 01:35

xerox2012
Messages: 15
Registered: October 2012

Junior Member

create table test(id varchar2(10), cost number);

INSERT INTO test VALUES('aa_ab',100);
INSERT INTO test VALUES('ab_ab',100);
INSERT INTO test VALUES('ab_ab',100);
INSERT INTO test VALUES('ab_ab',100);

I havea cursor which should return values basing on the id passed as:

if id like '%aa_' then it should sum cost for both ids 'aa_ab' and 'ab_ab' and print the id as 'ab_ab'
if id is like 'ab_%' then it should sum cost only for id 'ab_%' and print id as 'ab_ab'

so ideally my output should be:

If id like 'aa_%' then
id cost
------------------
ab_ab 400
If id like 'ab_%' then
id cost
------------------
ab_ab 300

Report message to a moderator

Re: Sum(cost) basing on id entered [message #567401 is a reply to message #567394]

Mon, 01 October 2012 02:13

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

Senior Member
Account Moderator

I gave you the answer last week; ok, it has been lost but you had it. Wink

I also made the same remark about posting last week, you seem to not understand it. Wink

SQL> select id, 
  2         sum(cost) + 
  3           nvl((select sum(cost) from test b
  4                where a.id like 'aa%' and b.id like 'ab%' and substr(b.id,4) = substr(a.id,4)),
  5               0) cost
  6  from test a
  7  group by a.id
  8  /
ID               COST
---------- ----------
ab_ab             300
aa_ab             400

Regards
Michel

Report message to a moderator

Re: Sum(cost) basing on id entered [message #570138 is a reply to message #567401]

Mon, 05 November 2012 22:31

priya13
Messages: 8
Registered: November 2012

Junior Member

Thanks Michel, it worked

Report message to a moderator

Previous Topic:	Create Tables Error Statement
Next Topic:	dates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 18 18:17:54 CDT 2013

Total time taken to generate the page: 0.10613 seconds