How to find the total number of item grouped by month or week in MySQL

Question

I have the following db table which shows missed homework.

CREATE TABLE `missed_homework` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `studentid` int(10) NOT NULL,
  `subjectid` int(10) NOT NULL,
  `assignment_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `teacherid` int(10) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ... ;


INSERT INTO `missed_homework` (`id`, `studentid`, `subjectid`, `assignment_name`, `teacherid`, `date`) VALUES
(1, 29, 5, '5E', 20, '2012-10-18 13:58:40'),
(2, 15, 5, '32B', 20, '2012-10-18 13:59:54'),
(3, 29, 4, 'Q2A', 20, '2012-10-18 17:53:46'),
(4, 29, 11, '6E', 20, '2012-10-02 20:06:39'),
(5, 29, 11, 'C15', 20, '2012-10-16 20:06:30'),
(6, 15, 11, '7A', 20, '2012-09-19 20:08:05'),
(7, 29, 5, '3B', 20, '2012-09-14 20:08:12'),
(8, 29, 13, '6E', 32, '2012-10-18 20:23:46'),
(9, 29, 11, '7E', 18, '2012-10-20 14:35:14')......

I am not sure how to do the followings.

I want to find total number of missed homework by say, studentid=29 grouped by month.
same as above except grouped by week.

I tried the followings but it does not output what I want.

 $this->db->where('studentid',$id);
    $this->db->from('missed_homework');
    $this->db->group_by('date');
    $query=$this->db->get();

how do you that a student have missed the homework? do you have a full list of homeworks? — John Woo, Oct 20 '12 at 06:32
I didn't downvoted your question. I was just asking for follow-up question. — John Woo, Oct 20 '12 at 06:35
have a look at [link](http://dev.mysql.com/doc/en/date-and-time-functions.html) that should explain how to get the group value — Sir Rufo, Oct 20 '12 at 06:36
also look at http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year — Sir Rufo, Oct 20 '12 at 06:38

John Woo · Accepted Answer · 2012-10-20T06:44:52.193

2

I have used DATE_FORMAT() to get the name of the month and the number of week for each date.

The following is the answer to your Question #1

SELECT  studentID, 
        DATE_FORMAT(`date`, '%M') `month`,
        COUNT(studentID) totalMissed
FROM hw_homework
-- WHERE studentID = ''
GROUP BY studentID, DATE_FORMAT(`date`, '%M')

SQLFiddle Demo

for Question #2

SELECT  studentID, 
        DATE_FORMAT(`date`, '%U') `WeekNo`,
        COUNT(studentID) totalMissed
FROM hw_homework
-- WHERE studentID = ''
GROUP BY studentID, DATE_FORMAT(`date`, '%U')

SQLFiddle Demo

edited Oct 20 '12 at 06:44

answered Oct 20 '12 at 06:39

John Woo

258,903
69
498
492

@shin just uncomment the commented `WHERE` clause, `WHERE studentID = 29` – John Woo Oct 20 '12 at 06:50
Can I add WHERE in the query like this?? WHERE IF ($id is not empty, studentid=$id, ''). I want to add if $id is not empty then WHERE studentid=$id and if it is empty, then no WHERE – shin Oct 20 '12 at 07:06
@shin try, `WHERE studentID = IF(CHAR_LENGTH($id) = 0, studentID, $id)` – John Woo Oct 20 '12 at 07:10

How to find the total number of item grouped by month or week in MySQL

1 Answers1

SQLFiddle Demo

SQLFiddle Demo