Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004

    Unanswered: help with group by

    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

  2. #2
    Join Date
    Jul 2004
    Dundee, Scotland
    regarding the correct usage of the WEEK function:


    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.
    Last edited by yellowmarker; 07-30-04 at 15:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts