Finding Weekly Summary from a table

Question

I am here to write a SQL statement for finding the weekly summary from a table. I had a table with following fields:

UIN, Date, Staff, work_hours

Now I would like to gather information how many hours has a staff worked in one week.

Answer 1

It's hard to tell from your question, but if you're looking for the total hours in one week by each employee, try the following:

SELECT Staff, SUM(work_hours)
FROM YourTable
WHERE Date BETWEEN week_start_day AND week_end_day
GROUP BY Staff

Otherwise, if you're looking for a full report, summarized by week, you can try the following, which will give you each staff's weekly hours, aggregated by week:

SELECT YEAR(Date), WEEK(Date), Staff, SUM(work_hours)
FROM YourTable
GROUP BY YEAR(Date), WEEK(Date), Staff
ORDER BY YEAR(Date), WEEK(Date)

Answer 2

You may want to try something like the following:

SELECT    staff, 
          WEEK(date) week_no, 
          SUM(work_hours) as work_hours_sum
FROM      log
GROUP BY  staff, WEEK(date)
ORDER BY  WEEK(date), staff;

Test case (MySQL):

CREATE TABLE log (
    uin int auto_increment primary key, 
    date date, 
    staff int, 
    work_hours int
);

INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-01', 1, 5);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-01', 2, 7);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-02', 1, 2);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-02', 2, 1);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-08', 1, 2);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-08', 2, 5);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-09', 1, 6);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-09', 2, 5);

Result:

+-------+---------+----------------+
| staff | week_no | work_hours_sum |
+-------+---------+----------------+
|     1 |      22 |              7 |
|     2 |      22 |              8 |
|     1 |      23 |              8 |
|     2 |      23 |             10 |
+-------+---------+----------------+
4 rows in set (0.00 sec)