Hi, I’m relatively new to MySQL and have an issue with the Group By command. I have search through the posts here and have not seen a similar issue.

I have a select statement:

SELECT Date_Format(CallDate, '%W, %M %d, %Y') As 'Date' ,Count(*) As 'Total Dips' ,(Count(*)-Sum(Billable)) As 'Billable Dips' ,Sum(Billable) As 'Test Dips' ,(Count(*)-Sum(Billable))*.01 As 'Revenue Dips' ,Sum(StatusCode='0' And CallDuration>'0') As 'Completed Calls' ,Sum(StatusCode='0' and CallDuration='0') As 'Not Answered',Sum(StatusCode>'0' ) As 'Calls Blocked' ,Sec_To_Time(Sum(CallDuration)) As 'Total Outbound Duration' ,Sec_To_Time(Avg(CallDuration)) As 'Average Duration',Sec_To_Time(Max(CallDuration)) As 'Maximum Duration' FROM CDRData2004 group by CallDate Order By CallDate

Which works correctly. Now if I change the ‘group by CallDate’ to ‘group by Month(CallDate)’ it works correctly. My problem is when I use ‘group by Week(CallDate)’.

I get the following dates for the output:
Thursday, June 24, 2004
Monday, June 28, 2004
Thursday, July 08, 2004
Sunday, July 11, 2004
I was expecting to see the Sunday date for the week (Sunday to Saturday).

Am I not understanding or applying the week() correctly?

Thanks in advance

regarding the correct usage of the WEEK function:

WEEK(date[,mode])

the mysql manual suggests that the default week starts on Sunday before MySQL version 4.0.14. For versions >= 4.0.14 if the mode argument is omitted, the value of the default_week_format system variable is used.

so, to force the week to start on Sunday you should use WEEK(CallDate,0)

providing that the data exists in the table, it should return the results you expect.