Home » SQL & PL/SQL » SQL & PL/SQL » Sum(cost) basing on id entered (merged)
Sum(cost) basing on id entered (merged) [message #567386] Mon, 01 October 2012 00:39 Go to next message
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
Re: Sum of costs [message #567387 is a reply to message #567386] Mon, 01 October 2012 01:06 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

you are excepting this one...
select * from test where value like'%aa%'
Re: Sum of costs [message #567388 is a reply to message #567387] Mon, 01 October 2012 01:08 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Try like..
select sum(value) from test where value like'%aa%'
Re: Sum of costs [message #567389 is a reply to message #567388] Mon, 01 October 2012 01:10 Go to previous messageGo to next message
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
Re: Sum of costs [message #567391 is a reply to message #567386] Mon, 01 October 2012 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
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
Sum(cost) basing on id entered [message #567394 is a reply to message #567386] Mon, 01 October 2012 01:35 Go to previous messageGo to next message
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
Re: Sum(cost) basing on id entered [message #567401 is a reply to message #567394] Mon, 01 October 2012 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
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

Re: Sum(cost) basing on id entered [message #570138 is a reply to message #567401] Mon, 05 November 2012 22:31 Go to previous message
priya13
Messages: 8
Registered: November 2012
Junior Member
Thanks Michel, it worked
Previous Topic: Create Tables Error Statement
Next Topic: dates
Goto Forum:
  


Current Time: Sat Aug 30 13:49:16 CDT 2014

Total time taken to generate the page: 0.12701 seconds