Home » SQL & PL/SQL » SQL & PL/SQL » summation of multiple values for a particular at specific period (oracle 10.g)
summation of multiple values for a particular at specific period [message #657269] Thu, 03 November 2016 11:18 Go to next message
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 59 times)
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 Go to previous messageGo to next message
BlackSwan
Messages: 25797
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 
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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 
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 Go to previous messageGo to next message
eogyamfi
Messages: 5
Registered: November 2016
Junior Member
Attached is the file
  • Attachment: ITEM.csv
    (Size: 1.39KB, Downloaded 76 times)
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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.
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 Go to previous messageGo to next message
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

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 Go to previous messageGo to next message
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')
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 Go to previous messageGo to next message
joy_division
Messages: 4817
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.
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 Go to previous messageGo to next message
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.
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 Go to previous message
Michel Cadot
Messages: 65317
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?

Previous Topic: Query Help
Next Topic: Selecting the columns
Goto Forum:
  


Current Time: Sat Jan 20 04:29:55 CST 2018

Total time taken to generate the page: 0.03359 seconds