SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist comfixit's Avatar
    Join Date
    Dec 2004
    Location
    Pasadena
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with a SubGrouping Query

    I am running the following query:

    Code:
    SELECT from_unixtime(swp.dateline, '%Y %M %D') as datelineDates, s.fullname, count(*) as totalTickets FROM swticketposts swp, swstaff s
    WHERE s.staffid=swp.staffid
    Group By swp.dateline,s.fullname
    And getting results similar to

    '2008 December 1st', 'Brandon', '1'
    '2008 December 1st', 'Brandon', '1'
    '2008 December 1st', 'Joe', '1'
    '2008 December 1st', 'Joe', '1'
    '2008 December 1st', 'Joe', '1'
    '2008 December 1st', 'Joe', '1'
    '2008 December 1st', 'Joe', '1'
    '2008 December 1st', 'Brandon', '1'
    '2008 December 1st', 'Joe', '1'
    '2008 December 1st', 'Brandon', '1'
    '2008 December 1st', 'Cherie', '1'
    '2008 December 1st', 'Brandon', '1'

    What I would like is to see something like:

    '2008 December 1st', 'Joe', '6'
    '2008 December 1st', 'Brandon', '5'
    '2008 December 1st', 'Cherie', '1'

    Where am I going wrong with this query?

  2. #2
    SitePoint Evangelist comfixit's Avatar
    Join Date
    Dec 2004
    Location
    Pasadena
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So I played around a bit with it and think I got it by doing the following:

    Code:
    SELECT from_unixtime(swp.dateline, '%Y %M %D') as datelineDates, s.fullname, count(*) as TotalTickets FROM swticketposts swp, swstaff s
    WHERE s.staffid=swp.staffid
    Group By datelineDates,s.fullname

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that is correct. but do yourself a favor and learn how to use the JOIN keyword instead of listing multiple table in the FROM clause.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    SitePoint Evangelist comfixit's Avatar
    Join Date
    Dec 2004
    Location
    Pasadena
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have used the Join keyword before and find that it seems a bit messy for some of these type of more straightforward SQL statements.

    What is the benefit of using the Join keyword over the way I am handling it?

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    in this particular situation, there is probably minimal benefit.

    but in general, using explicit JOINs as much as possible makes queries easier to read and gives the optimizer additional hints as to the most efficient execution plan. it also makes it easier to not forget to specify the relationships between your tables when writing a query.

    i typically reserve the comma-delimited table list format for time when i need a Cartesian product.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by longneck View Post
    i typically reserve the comma-delimited table list format for time when i need a Cartesian product.
    use CROSS JOIN for that

    now you have no reason to use comma list syntax at all

    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
  •