CODEWITHSUNDEEP
phpmysql
Akai shur
Akai shur

Reputation: 187

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.

Upvotes: 1

Views: 1646

Answers (1)

Rahul
Rahul

Reputation: 18557

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  YEAR(o_date), MONTH(o_date)

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

EDIT

Week's related data is correct, as it is giving all data of that week only.

For months,

For month

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

Upvotes: 1

Related Questions