#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Posts
    11
    Rep Power
    0

    MySQL grouping not working for me.


    I have a query that I am trying to group that I am not getting the results that I need in terms of it grouping them here is the query:

    SELECT c.login, count(cc.case_id) SESSION_ID, SITE_NAME, Manager,
    (Select CONCAT(WEEK(c.Comm_date_UTC))
    from communications
    group by 'week')
    from case_details cd
    join case_communications cc on cd.case_id=cc.case_id
    join communications c on cc.comm_id=c.comm_id
    where CONVERT_TZ(cc.Creation_DATE,'+00:00','+10:00')
    BETWEEN DATE('2013-1-1') AND CURRENT_DATE ( )
    Group by c.login
    limit 100;

    So what this is doing is giving me the following:



    So the grouping by the login is working, but not buy the week, what I wanted to see is:



    Note that its grouped by the week and login now, this is what I am trying to get

    Any help?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    try like this --
    Code:
    SELECT c.login
         , COUNT(cc.case_id) AS the_count
         , session_id
         , site_name
         , manager
         , WEEK(c.comm_date_utc) AS the_week
      FROM case_details AS cd
      JOIN case_communications AS cc 
        ON cc.case_id = cd.case_id
      JOIN communications AS c
        ON c.comm_id = cc.comm_id
     WHERE CONVERT_TZ(cc.Creation_DATE,'+00:00','+10:00') 
           BETWEEN DATE('2013-1-1') AND CURRENT_DATE 
    GROUP
        BY c.login
         , the_week
    LIMIT 100;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo