Home » SQL & PL/SQL » SQL & PL/SQL » how can don't use column in group by
| how can don't use column in group by [message #667882] |
Tue, 23 January 2018 08:05  |
 |
hassan08
Messages: 123
Registered: June 2011
Location: egypt
|
Senior Member |
|
|
i have table structure
CREATE TABLE TEST1(EMP_CODE NUMBER, ATTEND_DATE DATE, HOURS_TOTAL NUMBER)
Insert into TEST1
(EMP_CODE, ATTEND_DATE, HOURS_TOTAL)
Values
(360, TO_DATE('01/21/2018 15:58:30', 'MM/DD/YYYY HH24:MI:SS'), 10);
Insert into TEST1
(EMP_CODE, ATTEND_DATE, HOURS_TOTAL)
Values
(320, TO_DATE('01/21/2018 15:58:50', 'MM/DD/YYYY HH24:MI:SS'), 20);
Insert into TEST1
(EMP_CODE, ATTEND_DATE, HOURS_TOTAL)
Values
(360, TO_DATE('01/23/2018 15:59:01', 'MM/DD/YYYY HH24:MI:SS'), 20);
Insert into TEST1
(EMP_CODE, ATTEND_DATE, HOURS_TOTAL)
Values
(320, TO_DATE('01/23/2018 15:59:23', 'MM/DD/YYYY HH24:MI:SS'), 15);
COMMIT;
i want show the following
emp_code,attend_date, hours_total
360 , 01/21/2018, 30
360 , 01/23/2018, 30
320 , 01/21/2018 , 35
320 ,01/23/2018, 35
[Updated on: Tue, 23 January 2018 08:09] Report message to a moderator |
|
|
|
| Re: how can don't use column in group by [message #667883 is a reply to message #667882] |
Tue, 23 January 2018 08:08  |
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You need the analytic version of sum:
SQL> select emp_code, attend_date, sum(hours_total) over (partition by emp_code) as hours_total from test1;
EMP_CODE ATTEND_DATE HOURS_TOTAL
---------- ----------- -----------
320 01/21/2018 35
320 01/23/2018 35
360 01/23/2018 30
360 01/21/2018 30
|
|
|
|
| Re: how can don't use column in group by [message #667901 is a reply to message #667882] |
Tue, 23 January 2018 13:09 |
|
hassan08
Messages: 123
Registered: June 2011
Location: egypt
|
Senior Member |
|
|
thank you for your replay
but when added this code i faced this error
numtodsinterval(sum(substr(attend_date, 12, 2)*3600 + substr(attend_date, 15, 2)*60 + substr(attend_date
over (partition by emp_code) as hours_total from test1
ORA-00923: FROM keyword not found where expected
full code
select
emp_code, attend_date,
numtodsinterval(sum(substr(attend_date, 12, 2)*3600 + substr(attend_date, 15, 2)*60 + substr(attend_date, 18, 2)), 'SECOND')
over (partition by emp_code) from test1;
[Updated on: Tue, 23 January 2018 13:10] Report message to a moderator |
|
|
|
| Re: how can don't use column in group by [message #667902 is a reply to message #667901] |
Tue, 23 January 2018 13:48 |
 |
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
select
emp_code,
attend_date,
--
numtodsinterval(sum(substr(attend_date, 12, 2)*3600 +
substr(attend_date, 15, 2)*60 +
substr(attend_date, 18, 2)
) over (partition by emp_code),
'SECOND')
from test; |
|
|
|
| Re: how can don't use column in group by [message #667905 is a reply to message #667902] |
Tue, 23 January 2018 14:57 |
Solomon Yakobson
Messages: 3280
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Very bad suggestion. Look how OP calculates date value in seconds using substr(attend_date, 12, 2). OP is implicitly converting date to string thinking it will return 'MM/DD/YYYY HH24:MI:SS' which tells me OP has no understanding of dates.
SY.
|
|
|
|
|
|
| Re: how can don't use column in group by [message #667936 is a reply to message #667906] |
Wed, 24 January 2018 14:45 |
|
hassan08
Messages: 123
Registered: June 2011
Location: egypt
|
Senior Member |
|
|
thank you the code in working perfect
select
emp_code,
attend_date,
--
numtodsinterval(sum(substr(attend_date, 12, 2)*3600 +
substr(attend_date, 15, 2)*60 +
substr(attend_date, 18, 2)
) over (partition by emp_code),
'SECOND')
from test;
CREATE TABLE TEST1
(
EMP_CODE NUMBER,
ATTEND_DATE DATE,
LEAVE_DATE DATE,
)
Insert into TEST1
(EMP_CODE, ATTEND_DATE, HOURS_TOTAL, LEAVE_DATE)
Values
(360, TO_DATE('01/21/2018 15:58:30', 'MM/DD/YYYY HH24:MI:SS'), '50:20', TO_DATE('01/21/2018 15:10:30', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
(EMP_CODE, ATTEND_DATE, HOURS_TOTAL, LEAVE_DATE)
Values
(320, TO_DATE('01/21/2018 15:58:50', 'MM/DD/YYYY HH24:MI:SS'), '50:30', TO_DATE('01/21/2018 12:58:50', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
(EMP_CODE, ATTEND_DATE, HOURS_TOTAL, LEAVE_DATE)
Values
(360, TO_DATE('01/23/2018 15:59:01', 'MM/DD/YYYY HH24:MI:SS'), '50:00', TO_DATE('01/21/2018 22:58:50', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEST1
(EMP_CODE, ATTEND_DATE, HOURS_TOTAL, LEAVE_DATE)
Values
(320, TO_DATE('01/23/2018 15:59:23', 'MM/DD/YYYY HH24:MI:SS'), '50:30', TO_DATE('01/21/2018 15:10:50', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
if want add the column leave_date to your code how can execute this try but not working with me
select
emp_code,
attend_date,
leave_date
numtodsinterval(sum(substr(attend_date, 12, 2)*3600 +
substr(attend_date, 15, 2)*60 +
substr(attend_date, 18, 2)*3600+
substr(leave_date,12,2)*60+
substr(leave_date,15,2)*60+
substr(attend_date, 18, 2)
) over (partition by emp_code),
'SECOND')
from test1;
the result must be like this
emp_code 360 11:07 as total_hours
emp_code 320 24:08 total_hours
|
|
|
|
| Re: how can don't use column in group by [message #667941 is a reply to message #667936] |
Thu, 25 January 2018 04:14 |
cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) Your create table has 3 columns, your insert has 4. Check your test case works before posting please.
2) As the others already mentioned you should never use substr to extract bits of a date. Should anyone change the nls_date_format (and it can be changed at session level) that code will break on the spot.
Here's a simpler way to get seconds from a date:
SQL> select sysdate as current_time, (sysdate-trunc(sysdate))*24*60*60 as seconds_since_minight from dual;
CURRENT_TIME SECONDS_SINCE_MINIGHT
--------------- ---------------------
20180125 110411 39851
|
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:15:39 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
