PHP MySQL query- group by date in datetime field

Question

I've been searching and I know there are similar questions, but none of them seems to answer this particular question.

I am trying to get a count of the total number of days an employee has worked on a given schedule. To do this, I am counting the total number of rows the employee appears on the "schedules" table. Only we run into a problem if the employee is scheduled twice on the same day.

To solve this, I want to count total number of rows and sort by DATE in a DATETIME field.

Current query:

$days = mysql_query("SELECT emp_id FROM schedules 
                     WHERE sch_id = '$sch_id' 
                     AND emp_id = '$emp_data[emp_id]'"); 

$tot_days = mysql_num_rows($days);

I would like to change it to:

$days = mysql_query("SELECT emp_id FROM schedules 
                     WHERE sch_id = '$sch_id' 
                     AND emp_id = '$emp_data[emp_id]'
                     GROUP BY start_date"); 
// "start_date" is a datetime field. Need to sort by date only YYYY-MM-DD
$tot_days = mysql_num_rows($days);

Any thoughts?

So what's wrong with the 2nd query? Isn't that what you want? If you are displaying the dates and wanted it ordered just add ORDER BY start_date to the end of the 2nd one. — Aaron W.
– Aaron W., Commented Oct 13, 2011 at 13:56
That would order by the exact datetime. Datetime goes down to the second- no two would be alike. This needs to group as far as the date only, not including the time. — Oseer
– Oseer, Commented Oct 13, 2011 at 14:00

Jeremy Smyth · Accepted Answer · 2011-10-13 14:12:07Z

2

If your start_date column is a MySQL datetime type, you could use the following:

$days = mysql_query("SELECT start_date, count(*) FROM schedules 
                 WHERE sch_id = '$sch_id' 
                 AND emp_id = '$emp_data[emp_id]'
                 GROUP BY DATE(start_date)
                 HAVING count(*) > 1
                 ORDER BY DATE(start_date)");

The DATE function "Extracts the date part of the date or datetime expression" http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date

This will give you only those rows where the emp_id being considered is used more than once in a given date. Remove the HAVING line if you want to see all.

edited Oct 13, 2011 at 14:12

answered Oct 13, 2011 at 14:06

Jeremy Smyth

23.6k2 gold badges57 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP MySQL query- group by date in datetime field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related