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 Go to next message
hassan08
Messages: 115
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 
i want made aggregation on the column hours_total for every employees

[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 Go to previous messageGo to next message
cookiemonster
Messages: 13333
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 Go to previous messageGo to next message
hassan08
Messages: 115
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 Go to previous messageGo to next message
Littlefoot
Messages: 21446
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
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 #667906 is a reply to message #667905] Tue, 23 January 2018 15:19 Go to previous messageGo to next message
Littlefoot
Messages: 21446
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So true. I didn't even look at table description. Seeing SUBSTR on ATTEND_DATE, I presumed that value *must* be stored in a VARCHAR2 column and simply fixed syntax error.
Re: how can don't use column in group by [message #667936 is a reply to message #667906] Wed, 24 January 2018 14:45 Go to previous messageGo to next message
hassan08
Messages: 115
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 Go to previous message
cookiemonster
Messages: 13333
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

Previous Topic: Special grouping with SQL
Next Topic: Alternate to DECODE function to be used in elsif statment in PLSQL procedure
Goto Forum:
  


Current Time: Wed Nov 14 21:50:42 CST 2018