CODEWITHSUNDEEP
javascriptphpmysql
Hamelraj
Hamelraj

Reputation: 4826

How to get sum of particular month SQL query?

my table -> id, employee,in_time,out_time,note (i have another table emloyee table) im using this query to generate this is my JSP page Excel document ->

public function getMainQuery(){
        $query = "SELECT 
        (SELECT concat(`first_name`,' ',`middle_name`,' ', `last_name`) from Employees where id = employee) as 'Employee',
        DATE_FORMAT(in_time, '%Y-%m-%d') as 'Date',
        DATE_FORMAT(in_time, '%H:%i:%s') as 'Time In ',
        DATE_FORMAT(out_time, '%H:%i:%s') as 'Time Out ',
    DATE_FORMAT(out_time, '%i')- DATE_FORMAT(in_time, '%i')),as 'WorkingHours'
        FROM Attendance ";

        return $query;
}

Im generate this data in my excel ->

 Employee                       Date    Time In Time Out    Working Hours

    Kasun Sameera Gunasekara    4/10/2015   8:50:21 19:02:47    10:12
    Kasun Sameera Gunasekara    4/9/2015    8:48:38 18:20:08    9:32
    Kasun Sameera Gunasekara    4/8/2015    9:08:47 17:35:57    8:27
    Kasun Sameera Gunasekara    4/7/2015    9:35:16 18:21:59    8:46
    Jehan  Rodrigo              4/7/2015    9:45:10 18:19:22    8:34
    Kasun Sameera Gunasekara    4/7/2015    9:35:16 18:21:59    8:46
    Rajitha Wickrama            4/7/2015    9:13:18 18:26:16    9:13
    Maurice A. PRIYANSITH P     4/7/2015    9:09:39 18:13:10    9:04
    Shalika Nadee Sirithunga    4/7/2015    9:09:04 18:07:24    8:58

    Kasun Sameera Gunasekara    4/2/2015    9:09:59 19:29:33    10:20
    Kasun Sameera Gunasekara    4/1/2015    8:51:29 18:00:29    9:09

But i want generate deatils like this ->

 Employee                   Month  Totalworkinghours
 Kasun Sameera Gunasekara   April   55.59

Upvotes: 1

Views: 151

Answers (2)

David
David

Reputation: 532

you must use GROUP BY to get summary of a field and use SUM to get the total for that GROUP BY. read https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

try this to

SELECT 
    a.employee AS employee_id,
    concat(e.`first_name`,' ',e.`middle_name`,' ',e.`last_name`) as employee_name,
    DATE_FORMAT(out_time, '%M') as month_name,
    SUM(TIMESTAMPDIFF(HOUR, a.in_time, a.out_time)) AS total_working_hours,
    SUM(TIMESTAMPDIFF(SECOND, a.in_time, a.out_time)) AS total_working_seconds
FROM Attendance AS a 
INNER JOIN Employees AS e ON e.id = a.employee
WHERE a.in_time BETWEEN '2015-01-01 00:00:00' AND '2015-12-31 23:59:59'
GROUP BY a.employee, month_name

you should consider using the _total_working_seconds_ because it's more preciese

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Use Str_to_date or Month with Group by

Select TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(Working Hours))), "%h:%i") 
From table    
Where Month(Str_to_date(date,'%d/%m/%y'))='4' 
Group by Month(Str_to_date(date,'%d/%m/%y'))

Upvotes: 1

Related Questions