mySQL Select Query Statement

Question

in my Table attendance

Insert Into tblAttendance(cEmpID,cCode,dDate,notMin) Values
('1000','R' , 120,'2016-10-27'),
('1000','R' , 120,'2016-10-28'),
('1000','S' , 120,'2016-10-29'),
('1000','L' , 120,'2016-10-30'),
('1001','R' , 120,'2016-10-27'),
('1001','R' , 120,'2016-10-28'),
('1001','S' , 120,'2016-10-29'),
('1001','L' , 120,'2016-10-30')

i need to sum all OT Min. per Code the output something like this.. EmpID,R-Total,S-Total,L-Total

here's my Sample Query

Select (Select sum(nOTMin) from tblattenddetail Where cCode='R') 'R-Total',
       (Select sum(nOTMin) from tblattenddetail Where cCode='S') 'S-Total',
       (Select sum(nOTMin) from tblattenddetail Where cCode='L') 'L-Total'

i need to Include the cEmpID.. Please Help me how will i revise this to Include the cEmpID..

jpw · Accepted Answer · 2016-10-30 15:13:26Z

1

You can use a case expression to conditionally aggregate the correct values. This should do what you want:

SELECT
  cEmpID, 
  SUM(CASE WHEN cCode='R' THEN nOTMin ELSE 0 END) AS 'R-Total',
  SUM(CASE WHEN cCode='S' THEN nOTMin ELSE 0 END) AS 'S-Total',
  SUM(CASE WHEN cCode='L' THEN nOTMin ELSE 0 END) AS 'L-Total'
FROM tblAttendance
GROUP BY cEmpID

Sample SQL Fiddle

answered Oct 30, 2016 at 15:13

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Spiegel Over a year ago

Why no one told me that you can use single quotes for column aliases? This would save me like 5 minutes of my life ;-)

Collectives™ on Stack Overflow

mySQL Select Query Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related