Home » SQL & PL/SQL » SQL & PL/SQL » sum (merged 4 now)
sum (merged 4 now) [message #321678] Wed, 21 May 2008 00:55 Go to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
i have a table named as experience detais.it contains following columns applicationno
company_name
from
to
total_year_of_experience
i want sum of year_of_experience in the following format 5 years 10 months and group by applicant_no

i write following code
select applicant_id,sum(to_char(total_year_experience,'yyyy,mm'))from experience_det group by applicant_id
*
ERROR at line 1:
ORA-01722: invalid number
send the code

[Updated on: Wed, 21 May 2008 00:56]

Report message to a moderator

Re: sum [message #321681 is a reply to message #321678] Wed, 21 May 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SUM is only allowed on a NUMBER not on STRING.
Send a description (DESCRIBE command) of the table.

Regards
Michel
Re: sum [message #321686 is a reply to message #321681] Wed, 21 May 2008 01:25 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
table design


Name Null? Type
----------------------------------------- -------- ----------------------------
total_year_experience VARCHAR2(30)
OFFICE VARCHAR2(10)
POST_HELD VARCHAR2(30)
POST_TYPE VARCHAR2(10)
DOJ1 DATE
DOE1 DATE
SCALE_OF_PAY VARCHAR2(20)
NATURE_OF_DUTY VARCHAR2(10)
SECTOR VARCHAR2(20)
APP_NO VARCHAR2(70)
APPLICANT_ID NUMBER(10)
Re: sum [message #321696 is a reply to message #321686] Wed, 21 May 2008 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wrong table.

In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Use the "Preview Message" button to verify.

Regards
Michel
Re: sum [message #321715 is a reply to message #321696] Wed, 21 May 2008 02:13 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
total_year_experience varchar2(30)
Re: sum [message #321778 is a reply to message #321715] Wed, 21 May 2008 05:20 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is its contents? Regarding one of your previous posts, it appears that it *might* be something like "2008, 03" or "1957, 12" etc.

If so, what would be sum of these values?
If not, why don't you finally take a time, read OraFAQ Forum Guide and learn how to properly ask a question? If we had CREATE TABLE and INSERT INTO sample data, it might be easier to answer your question.
Re: sum [message #321806 is a reply to message #321715] Wed, 21 May 2008 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
total_year_experience varchar2(30)

So "to_char(total_year_experience,'yyyy,mm')" is meaningless.

Regards
Michel

Re: sum [message #321830 is a reply to message #321686] Wed, 21 May 2008 07:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
varosh81 wrote on Wed, 21 May 2008 02:25
table design


Name Null? Type
----------------------------------------- -------- ----------------------------
total_year_experience VARCHAR2(30)
OFFICE VARCHAR2(10)
POST_HELD VARCHAR2(30)
etc.



If this actually is the output from your DESC command, then you have a bigger problem as you will have to refer to total_year_experience is double quotes in all yor code all the time because you were short-sighted when you created the table and used double quotes for the column name.

And if it was not the actual output of a DESC command, why would you spend all the effort to retype an output of a DESC command, which is prone to errors, rather than take 2 second to cut and paste the actual output.
sum [message #322791 is a reply to message #321678] Mon, 26 May 2008 04:36 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
I have an column named as years of experience.In that column contains values like 2years5months,5years7months,3years2months etc.i want sum of this column and also group by function.
send the sql code.
Re: sum [message #322794 is a reply to message #322791] Mon, 26 May 2008 04:38 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

send sample table and datas
and also send what u did.


kanish
Re: sum [message #322795 is a reply to message #322791] Mon, 26 May 2008 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to repeat the question in another topic.
Just answer the questions we posted you in the previous one.

Regards
Michel
Re: sum [message #322798 is a reply to message #322791] Mon, 26 May 2008 04:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
varosh81 wrote on Mon, 26 May 2008 11:36
send the sql code.

Missing the magic word..
Re: sum [message #322803 is a reply to message #322798] Mon, 26 May 2008 05:12 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
select applicant_id,sum(years_of_experience) from experience_det group by applicant_id;
display error message
ERROR at line 1:
ORA-01722: invalid number
Re: sum [message #322808 is a reply to message #322794] Mon, 26 May 2008 05:17 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
kanish

i have an table named as experience_details.it contains colums like
applicant_id,office,from(date),to(date),year_of_experience(varchar2(25) etc
i want sum(years_of_experience)and group by function.
i have written following query
select applicant_id,sum(years_of_experience) from experience_det group by applicant_id;
display error message
ERROR at line 1:
ORA-01722: invalid number
Re: sum [message #322809 is a reply to message #322808] Mon, 26 May 2008 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the previous answers and supply the requested information.

Regards
Michel
Re: sum [message #322886 is a reply to message #322791] Mon, 26 May 2008 10:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
varosh81 wrote on Mon, 26 May 2008 11:36
I have an column named as years of experience.In that column contains values like 2years5months,5years7months,3years2months etc.i want sum of this column and also group by function.
send the sql code.

How much is the sum of '3apples2pears' and '1orange1apple' ?
Re: sum [message #322887 is a reply to message #322886] Mon, 26 May 2008 11:11 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
SQL> select sum(col) from (
  2  select '3apples2pears' col from dual
  3  union
  4  select '1orange1apple' col from dual
  5  );
sum(col)
---------------
fruit_salad

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release     9.2.0.8.0 - Production
CORE               9.2.0.8.0 - Production
Kitchen Utilities  0.0.0.0.1 - Alpha

SQL>
Re: sum [message #322900 is a reply to message #322887] Mon, 26 May 2008 14:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
lol..

Alas, the Kitchen Utilities option only works with unions.
We need a sum.
sql [message #330791 is a reply to message #321678] Tue, 01 July 2008 04:52 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
I have developed one form named as experience.
Here i have entered the sample data

Applicant id office years of experience

3001 dcw 2 years 3 months
3001 elnet 1 year 5 months

3002 spic 3 years 3 months
3002 serc 2 years 6 months

my question is when i retrieve the data in sql the output will come this format

Applicant id years of experience

3001 3 years 8months
3002 5 years 9 months

[Updated on: Tue, 01 July 2008 04:55]

Report message to a moderator

Re: sql [message #330793 is a reply to message #330791] Tue, 01 July 2008 05:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't store periods of time in a free-format varchar2 field. How can you expect to do calculations on that?
Re: sql [message #330794 is a reply to message #330791] Tue, 01 July 2008 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank is right, if you need to make computations on duration, use INTERVAL datatype.

Regards
Michel
Re: sql [message #330808 is a reply to message #330794] Tue, 01 July 2008 06:50 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
I have written query like this

select TO_yminterval('yearofexperience') from experience_det where applicant_id=3101
*
ERROR at line 1:
ORA-01867: the interval is invalid

kindly send the correct query

[Updated on: Tue, 01 July 2008 06:51]

Report message to a moderator

Re: sql [message #330826 is a reply to message #330808] Tue, 01 July 2008 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We don't have your data.
If you want more post test case: create table and insert statements along with the result you want with these data.

Regards
Michel
sum function [message #337491 is a reply to message #321678] Thu, 31 July 2008 01:30 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
how to write sql query for sum below values?

3 years 4 months
5 years 7 months

output will come like 8years 11 months

send solution

[Updated on: Thu, 31 July 2008 01:33]

Report message to a moderator

Re: sum function [message #337495 is a reply to message #337491] Thu, 31 July 2008 01:39 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
this data hold in table?
Re: sum function [message #337499 is a reply to message #337491] Thu, 31 July 2008 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Always the same answers for your 2 previous same posts.

Regards
Michel
Re: sum function [message #337500 is a reply to message #337499] Thu, 31 July 2008 01:59 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
didnt understand you Michel Cadot Embarassed
what it means?
Re: sum function [message #337501 is a reply to message #337499] Thu, 31 July 2008 02:01 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
sir
i wrote query like this

select sum(experience) from experiencedetails where empno=3101;
display following error

ERROR at line 1:
ORA-01722: invalid number

table structure

empno experience

3101 3 years 2 months

3101 5 years 4 months

[Updated on: Thu, 31 July 2008 02:03]

Report message to a moderator

Re: sum function [message #337504 is a reply to message #337501] Thu, 31 July 2008 02:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Reread this thread.
Nothing changed, so neither will our answers.

[Updated on: Thu, 31 July 2008 02:05]

Report message to a moderator

Re: sum function [message #337518 is a reply to message #337500] Thu, 31 July 2008 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@ora_baby,
My post was not for you but for OP.

Regards
Michel
Re: sum (merged at least 3) [message #337613 is a reply to message #321678] Thu, 31 July 2008 07:19 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
send the code

ok, when you ask, the code shall be
SELECT applicant_id, sum_year_of_experience(total_year_experience)
FROM experience_det
GROUP BY applicant_id
Unfortunately, Oracle does not have built-in function for computing sums on miscellaneous string garbage, so you have to write your own. You may find useful this lik about using user defined aggregate function: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/aggr_functions.htm (there is also an example in the end); or inspire with STRAGG aggregate function (original AskTom thread http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:229614022562 is temporarily unavailable; but you searching for "Oracle STRAGG" may reveal you another sites containing it).
Next is on you - especially the rule to evaluate sum of two strings.
Enjoy.
Re: sum (merged 4 now) [message #337837 is a reply to message #321678] Fri, 01 August 2008 05:35 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Well as all the experts has mentioned your design itself is wrong and needs to be reconsidered. But if your data is constant
as you have given, then something like this might help you..


SQL> select * from test;

COL1
--------------------------------------------------------------------------------
2 years 3 months
2 years 3 months

SELECT TRUNC(SUM((SUBSTR(A,1,INSTR(A,',')-1) * 12) + (SUBSTR(A,INSTR(A,',')+1)))/12) ||  ' YEARS ' ||
	   MOD(SUM((SUBSTR(A,1,INSTR(A,',')-1) * 12) + (SUBSTR(A,INSTR(A,',')+1))),12) || ' MONTHS '
FROM
(
SELECT REPLACE(REPLACE(REPLACE(UPPER(col1),'YEARS',','),'MONTHS',''),' ','') A FROM TEST


This all depends on how your data is stored but really not a good way of doing it..

Regards
Re: sum (merged 4 now) [message #337852 is a reply to message #337837] Fri, 01 August 2008 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet sometimes there is only YEAR (with or without S) and sometimes only MONTH (same thing), also sometimes it is in upper case, or lower case, or mixed... Wink

Regards
Michel
Re: sum (merged 4 now) [message #337879 is a reply to message #321678] Fri, 01 August 2008 07:54 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Quote:
I bet sometimes there is only YEAR (with or without S) and sometimes only MONTH (same thing), also sometimes it is in upper case, or lower case, or mixed...

Regards
Michel



No doubt.. that is why I said it is not a good design at all but just a way around, as long as the format given is consistent... Smile
Re: sum (merged 4 now) [message #338224 is a reply to message #337837] Mon, 04 August 2008 04:23 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
himang

I applied your query.But following error displayed.kindly rectify the error.

query :
SELECT TRUNC(SUM((SUBSTR(A,1,INSTR(A,',')-1) * 12)
+(SUBSTR(A,INSTR(yearsofexperience,',')+1)))/12) || ' YEARS ' ||
MOD(SUM((SUBSTR(A,1,INSTR(A,',')-1) * 12) +
(SUBSTR(A,INSTR(A,',')+1))),12) || ' MONTHS '
FROM
(
SELECT REPLACE(REPLACE(REPLACE(UPPER(yearsofexperience),'YEARS',','),'MONTHS',''),' ','') A
FROM
experience_det

errormessage:

experience_det
*
ERROR at line 9:
ORA-00907: missing right parenthesis

[Updated on: Mon, 04 August 2008 04:25]

Report message to a moderator

Re: sum (merged 4 now) [message #338233 is a reply to message #338224] Mon, 04 August 2008 04:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The code you posted has an unmatched left bracket just after the FROM statement.

This is routine code debugging - do you really need our help for this?
Re: sum (merged 4 now) [message #338544 is a reply to message #338233] Tue, 05 August 2008 05:39 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
following error displayed in the sum function.Kindly correct
the error.


SELECT TRUNC(SUM((SUBSTR(A,1,INSTR(A,',')-1) * 12) +
(SUBSTR(A,INSTR(A,',')+1)))/12) || ' YEARS ' ||
MOD(SUM((SUBSTR(A,1,INSTR(A,',')-1) * 12) +
(SUBSTR(A,INSTR(A,',')+1))),12) || ' MONTHS '
FROM
(
SELECT REPLACE(REPLACE(REPLACE(UPPER(yearsofexperience),'YEARS',','),'MONTHS',''),' ','')
A FROM
experience_det)



/
(SUBSTR(A,INSTR(A,',')+1))),12) || ' MONTHS '
*
ERROR at line 4:
ORA-01722: invalid number
Re: sum (merged 4 now) [message #338549 is a reply to message #338544] Tue, 05 August 2008 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One more obvious error.

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).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: sum (merged 4 now) [message #338556 is a reply to message #338544] Tue, 05 August 2008 06:07 Go to previous message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

varosh81 wrote on Tue, 05 August 2008 16:09
following error displayed in the sum function.Kindly correct
the error.


SELECT TRUNC(SUM((SUBSTR(A,1,INSTR(A,',')-1) * 12) +
(SUBSTR(A,INSTR(A,',')+1)))/12) || ' YEARS ' ||
MOD(SUM((SUBSTR(A,1,INSTR(A,',')-1) * 12) +
(SUBSTR(A,INSTR(A,',')+1))),12) || ' MONTHS '
FROM
(
SELECT REPLACE(REPLACE(REPLACE(UPPER(yearsofexperience),'YEARS',','),'MONTHS',''),' ','')
A FROM
experience_det)



/
(SUBSTR(A,INSTR(A,',')+1))),12) || ' MONTHS '
*
ERROR at line 4:
ORA-01722: invalid number



As earlier mentioned... I said it is not a good design at all but just a way around, as long as the format given is consistent...
Previous Topic: Creating own Formulas
Next Topic: Lock Rows
Goto Forum:
  


Current Time: Mon Dec 05 11:17:36 CST 2016

Total time taken to generate the page: 0.08679 seconds