SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    The right way to use group by

    Hi,
    can anyone explain me what's the right way to use the group by clause ?

    for instance
    PHP Code:
    SELECT A.domain_idU.birthday AS fieldDATEaccess_start_datetime ) AS DATECOUNTA.id ) AS value 
    FROM stats_access A 
    JOIN users U ON U
    .id A.fb_id 
    GROUP BY DATE
    A.access_start_datetime ) , U.birthdayA.domain_id 
    with this query I get the number of the users group by access_start_datetime,birthday and domain_id
    Is it right ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, that looks fine

    why, don't you like the results it produces?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It works fine but I'd a doubt.
    All in all I'd like to know what's the
    rules to apply group by
    from http://www.tizag.com/mysqlTutorial/mysqlgroupby.php
    Some technical rules of GROUP BY:
    The column that you GROUP BY must also be in your SELECT statement.
    Remember to group by the column you want information about and not the one you are applying the aggregate function on. In our above example we wanted information on the type column and the aggregate function was applied to the price column.
    Is it enough ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "The column that you GROUP BY must also be in your SELECT statement" is correct, except i would say the SELECT clause, not SELECT statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok thanks, for instance
    PHP Code:
    SELECT typeCOUNT(nameFROM products GROUP BY type 
    in this case GROUP BY is mandatory or it's enough
    PHP Code:
    SELECT typeCOUNT(nameFROM products 

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it's mandatory

    it's kind of the reverse of "the column that you GROUP BY must also be in your SELECT clause"

    if you have a non-aggregate column in your SELECT clause, it must be in the GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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