OraFAQ Forum: SQL & PL/SQL » summation of multiple values for a particular at specific period

Home » SQL & PL/SQL » SQL & PL/SQL » summation of multiple values for a particular at specific period (oracle 10.g)

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

summation of multiple values for a particular at specific period [message #657269]

Thu, 03 November 2016 11:18

eogyamfi
Messages: 5
Registered: November 2016

Junior Member

I want to get the total number of days for particular person within a year. We i use the sum function, I am not getting the total number of the days for the person.below is the script and and the output.

select LVAH_EMP_CODE, EMP_NAME, EMP_DIVN_CODE, DIVN_NAME, EMP_DEPT_CODE,
DEPT_NAME, EMP_JOB_LONG_DESC,EXTRACT(YEAR FROM LVAH_APPR_START_DT) Years,sum(LVAH_APPR_DAYS) as days
from PV_EMP_ANN_LEAVE_JCM
where LVAH_LV_CATG_CODE='ANL05'
and LVAH_EMP_CODE='S20096'
group by LVAH_EMP_CODE, EMP_NAME, EMP_DIVN_CODE, DIVN_NAME, EMP_DEPT_CODE,
DEPT_NAME, EMP_JOB_LONG_DESC,EXTRACT(YEAR FROM LVAH_APPR_START_DT), LVAH_APPR_DAYS

Per the attached the number of days for 2015=18
2016=44

Attachment: ITEM.csv
(Size: 1.39KB, Downloaded 1267 times)

Report message to a moderator

Re: summation of multiple values for a particular at specific period [message #657270 is a reply to message #657269]

Thu, 03 November 2016 12:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

SELECT lvah_emp_code, 
       emp_name, 
       emp_divn_code, 
       divn_name, 
       emp_dept_code, 
       dept_name, 
       emp_job_long_desc, 
       Extract(year FROM lvah_appr_start_dt) Years, 
       SUM(lvah_appr_days)                   AS days 
FROM   pv_emp_ann_leave_jcm 
WHERE  lvah_lv_catg_code = 'ANL05' 
       AND lvah_emp_code = 'S20096' 
GROUP  BY lvah_emp_code, 
          emp_name, 
          emp_divn_code, 
          divn_name, 
          emp_dept_code, 
          dept_name, 
          emp_job_long_desc, 
          Extract(year FROM lvah_appr_start_dt), 
          lvah_appr_days

Report message to a moderator

Re: summation of multiple values for a particular at specific period [message #657271 is a reply to message #657269]

Thu, 03 November 2016 13:13

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

Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) 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.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Report message to a moderator

Re: summation of multiple values for a particular at specific period [message #657272 is a reply to message #657269]

Thu, 03 November 2016 13:16

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

Senior Member
Account Moderator

Quote:

I am not getting the total number of the days for the person.below is the script and and the output.

Of course, you get the number of days for "lvah_lv_catg_code = 'ANL05' AND lvah_emp_code = 'S20096'" per

GROUP  BY lvah_emp_code, 
          emp_name, 
          emp_divn_code, 
          divn_name, 
          emp_dept_code, 
          dept_name, 
          emp_job_long_desc, 
          Extract(year FROM lvah_appr_start_dt), 
          lvah_appr_days

Report message to a moderator

Re: summation of multiple values for a particular at specific period [message #657276 is a reply to message #657272]

Fri, 04 November 2016 02:48

eogyamfi
Messages: 5
Registered: November 2016

Junior Member

Attached is the file

Attachment: ITEM.csv
(Size: 1.39KB, Downloaded 1213 times)

Report message to a moderator

Re: summation of multiple values for a particular at specific period [message #657277 is a reply to message #657276]

Fri, 04 November 2016 02:50

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

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 03 November 2016 19:13

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) 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.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Report message to a moderator

Re: summation of multiple values for a particular at specific period [message #657282 is a reply to message #657277]

Fri, 04 November 2016 05:27

eogyamfi
Messages: 5
Registered: November 2016

Junior Member

CREATE TABLE PV_EMP_ANN_LEAVE_JCM
(
LVAH_EMP_CODE VARCHAR2(6 BYTE) NOT NULL,
EMP_NAME VARCHAR2,(50,byte)
EMP_DIVN_CODE VARCHAR2(4, BYTE) not NULL,
DIVN_NAME VARCHAR2(40,byte),
EMP_DEPT_CODE VARCHAR2(7, BYTE) not NULL,
DEPT_NAME VARCHAR2(40, BYTE),
EMP_JOB_LONG_DESC VARCHAR2(60, BYTE),
LVAH_LV_CATG_CODE VARCHAR2(10 BYTE) NOT NULL,
LVAH_APPR_START_DT DATE NOT NULL,
LVAH_APPR_DAYS number(4),
LV_TYPE_DESC VARCHAR2(40, BYTE)

)
TABLESPACE ORION
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

INSERT ALL INTO PV_EMP_ANN_LEAVE_JCM (LVAH_EMP_CODE,EMP_NAME,EMP_DIVN_CODE,DIVN_NAME,EMP_DEPT_CODE,DEPT_NAME,EMP_JOB_LONG_DESC,LVAH_LV_CATG_CODE,LVAH_APPR_START_DT,LVAH_A PPR_DAYS,LV_TYPE_DESC)
VALUES ('S20096','TAAH STEPHEN','S103', 'ACCOUNTING (SUHUMA)', 'S10301, 'ACC.PAYABLE (SUHUMA)','SENIOR ACCOUNTANT','ANL05','Annual Leave','20-JUN-2016',, 3,'P/L FOR 2016');
INTO PV_EMP_ANN_LEAVE_JCM(LVAH_EMP_CODE,EMP_NAME,EMP_DIVN_CODE,DIVN_NAME,EMP_DEPT_CODE,DEPT_NAME,EMP_JOB_LONG_DESC,LVAH_LV_CATG_CODE,LVAH_ APPR_START_DT,LVAH_APPR_DAYS,LV_TYPE_DESC)
VALUES ('S20096','TAAH STEPHEN','S103', 'ACCOUNTING (SUHUMA)', 'S10301, 'ACC.PAYABLE (SUHUMA)','SENIOR ACCOUNTANT','ANL05','Annual Leave','29-AUG-2016' 15,'P/L FOR 2016');
INTO PV_EMP_ANN_LEAVE_JCM(LVAH_EMP_CODE,EMP_NAME,EMP_DIVN_CODE,DIVN_NAME,EMP_DEPT_CODE,DEPT_NAME,EMP_JOB_LONG_DESC,LVAH_LV_CATG_CODE,LVAH_ APPR_START_DT,LVAH_APPR_DAYS,LV_TYPE_DESC)
VALUES ('S20096','TAAH STEPHEN','S103', 'ACCOUNTING (SUHUMA)', 'S10301, 'ACC.PAYABLE (SUHUMA)','SENIOR ACCOUNTANT','ANL05','CASH','20-MAY-2016', 26,'2015 LEAVE COMMUTTED TO CASH IN THE MONTH OF MAY 2016');
INTO PV_EMP_ANN_LEAVE_JCM (LVAH_EMP_CODE,EMP_NAME,EMP_DIVN_CODE,DIVN_NAME,EMP_DEPT_CODE,DEPT_NAME,EMP_JOB_LONG_DESC,LVAH_LV_CATG_CODE,LVAH_APPR_START_DT,LVAH_A PPR_DAYS,LV_TYPE_DESC)
VALUES ('S20089','MERCY ADU','S104', 'STORES (SUHUMA)', 'S10401, 'DATA INPUT CLERK (SUHUMA)','DATA INPUT CLERK','ANL05','Annual Leave','01-APR-2016', 1,'P/L FOR 2016');
INTO PV_EMP_ANN_LEAVE_JCM (LVAH_EMP_CODE,EMP_NAME,EMP_DIVN_CODE,DIVN_NAME,EMP_DEPT_CODE,DEPT_NAME,EMP_JOB_LONG_DESC,LVAH_LV_CATG_CODE,LVAH_APPR_START_DT,LVAH_A PPR_DAYS,LV_TYPE_DESC)
VALUES ('S20089','MERCY ADU','S104', 'STORES (SUHUMA)', 'S10401, 'DATA INPUT CLERK (SUHUMA)','DATA INPUT CLERK','ANL05','Annual Leave','30-APR-2016', 2,'P/L FOR 2016');
INTO PV_EMP_ANN_LEAVE_JCM (LVAH_EMP_CODE,EMP_NAME,EMP_DIVN_CODE,DIVN_NAME,EMP_DEPT_CODE,DEPT_NAME,EMP_JOB_LONG_DESC,LVAH_LV_CATG_CODE,LVAH_APPR_START_DT,LVAH_A PPR_DAYS,LV_TYPE_DESC)
VALUES ('S20089','MERCY ADU','S104', 'STORES (SUHUMA)', 'S10401, 'DATA INPUT CLERK (SUHUMA)','DATA INPUT CLERK','ANL05','Annual Leave','15-AUG-2016', 23,'P/L FOR 2016')

OUTPUT
CODE
S20089 DATA INPUT CLERK 2016 26
S20096 SENIOR ACCOUNTANT 2016 44

Report message to a moderator

Re: summation of multiple values for a particular at specific period [message #657285 is a reply to message #657277]

Fri, 04 November 2016 06:34

eogyamfi
Messages: 5
Registered: November 2016

Junior Member

Report message to a moderator

Re: summation of multiple values for a particular at specific period [message #657291 is a reply to message #657285]

Fri, 04 November 2016 07:59

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Looks to me like there is a missing close quote on all of your date strings. And they are strings, not dates as they are enclosed in quotes. My guess is that there are other errors.

Report message to a moderator

Re: summation of multiple values for a particular at specific period [message #657292 is a reply to message #657291]

Fri, 04 November 2016 09:57

eogyamfi
Messages: 5
Registered: November 2016

Junior Member

please the problem is getting the total of nos of days per the employee not the date.

Report message to a moderator

Re: summation of multiple values for a particular at specific period [message #657296 is a reply to message #657292]

Fri, 04 November 2016 11:04

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

Senior Member
Account Moderator

Do you read what we post you, or are you just following your thoughts and don't care about what we're saying?

Report message to a moderator

Previous Topic:	Query Help
Next Topic:	Selecting the columns

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 06:13:06 CDT 2024