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 |
|
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 1319 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 |
|
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
|
|
|
|
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: 68665 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 #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
|
|
|
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 |
|
|
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')
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:22:30 CDT 2024
|