Akaishu Akaishu - 4 years ago 94
MySQL Question

Sum per day, week and month

I do have a table with a field called "hours", that field is the time that my workers works per day, it creates a new registry for each day, so it's something like this:

Date: 2017-02-06; hours: 3
Date: 2017-02-07; hours: 8
Date: 2017-02-31; hours: 8


The question is, how can i do a select to sum all the hours for this day(that's already solved), for this week and for this month.

What I expect is this:

sum for this day(2017-02-06): 3

sum for this week(2017-02-06-2017--2017-02-12): 11

sum for this month (february): 19

Original query:

$db->query("select sum(hours) horasDia from table_hours where
user="'.$user.'" and DATE(inici)=date('Y-m-d'));


I'm doing this via php.

EDIT:
With the answer of rahul_m:

For day,

SELECT SUM(`hours`) horasDia
FROM table_hours
WHERE user="'.$user.'" and DATE(inici)= your_date;


For week,

SELECT SUM(`hours`) horasDia
FROM table_hours
WHERE user="'.$user.'" and DATE(inici)= your_date;
GROUP BY WEEK(your_date);


For month

SELECT SUM(`hours`) horasDia
FROM table_hours
WHERE user="'.$user.'" and DATE(inici)= your_date;
GROUP BY DATE_FORMAT(your_date, "%m-%Y");


In the week section it's giving me only the hours of the date 2017-01-31 (2017-01-30 week)

SELECT SUM(`hours`) horasDia
FROM table_hours
WHERE user="'.$user.'" and DATE(inici)= "2017-01-31";
GROUP BY WEEK("2017-01-31");


In the month section, it's giving me just nothing.

SELECT SUM(`hours`) horasDia
FROM table_hours
WHERE user="'.$user.'" and DATE(inici)= "2017-01-31";
GROUP BY DATE_FORMAT("2017-01-31", "%m-%Y");


Maybe I'm doing something wrong.

Answer Source

For day,

SELECT    SUM(`hours`) horasDia
FROM      table_hours 
WHERE     user="'.$user.'" and DATE(inici)= your_date;

For week,

SELECT    SUM(`hours`) horasDia
FROM      table_hours 
WHERE     user="'.$user.'" and DATE(inici)= your_date;
GROUP BY  WEEK(your_date);

For month

SELECT    SUM(`hours`) horasDia
FROM      table_hours 
WHERE     user="'.$user.'" and DATE(inici)= your_date;
GROUP BY DATE_FORMAT(your_date, "%m-%Y"); 

You will get all three details as per your requirements with this, give it a try.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download