MySQL By Week

In this section, you will learn about the MySQL By Week. If you want to get some weekly reports of the table data you will use the sql week() function.

MySQL By Week

MySQL By Week

     

In this section, you will learn about the MySQL By Week. If you want to get some weekly reports of the table data you will use the sql week() function.

Week(): MySQL has the function WEEK() which returns a week number. 

For example, 

Query:

SELECT WEEK('2008-01-01',0);

Ouput

 

Query: 

SELECT WEEK('2008-01-07',0);

Ouput:

 

Query: 

SELECT WEEK('2008-01-01',1);

Ouput:

 


Query: 

SELECT WEEK('2008-01-07',1);

Ouput:

 

The example below shows you how to use week function in conjunction with group by clause.

Table: employee

CREATE TABLE `employee` ( 
`emp_id` int(11) NOT NULL auto_increment, 
`emp_name` varchar(10) character set utf8 NOT NULL, 
`emp_salary` int(11) NOT NULL, 
`emp_startDate` datetime NOT NULL, 
`dep_name` varchar(50) NOT NULL, 
PRIMARY KEY (`emp_id`) 
)

Data in above table:

Use the week() function through the following Query:

SELECT sum(emp_salary) FROM employee GROUP BY WEEK(emp_startDate);

The above query sums the employee salary according to week of emp_startDate column.

Output: