CODEWITHSUNDEEP
sqlgroup-bynetezza
Jenna
Jenna

Reputation: 81

Unable to group values

I have the following table:

Emp_Id    In_Time                 Out_Time            
100     '2014-05-23 11:48:00'  '2014-05-23 17:00:00'     
100     '2014-05-23 11:48:00'  '2014-05-23 13:08:00'    
100     '2014-05-23 14:10:00'  '2014-05-23 17:40:00'  
100     '2014-05-23 17:10:00'  '2014-05-23 17:15:00'

I want to take the minimum of in and out times and present it like this:

Emp_Id   In_Time                Out_Time               
100     '2014-05-23 11:48:00'  '2014-05-23 17:40:00'

My SQL code is :

select Emp_Id, In_Time, Out_Time 
from table
where Emp_Id = 100
group by (Emp_Id, In_Time, Out_time)

On executing this code, I am getting the following table:

Emp_Id    In_Time                 Out_Time            
100     '2014-05-23 11:48:00'  '2014-05-23 17:15:00'     
100     '2014-05-23 11:48:00'  '2014-05-23 17:15:00'

There's a problem in grouping which I am not able to understand. Can someone help me with this please

Upvotes: 1

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you want one row in the result set per emp_id, then that should be the only expression in the group by. And, you need aggregation functions:

select Emp_Id, min(In_Time), max(Out_Time)
from table
where Emp_Id = 100
group by Emp_Id;

Upvotes: 1

Related Questions