Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Apr 2006
    Location
    Northbrook, IL
    Posts
    394
    Thanks
    8
    Thanked 6 Times in 6 Posts

    Question need some left join help

    i'd like to get all users with their order counts and order totals, even if the users have no orders. this is what i got and it only shows users who have orders in the specified time period...like an INNER JOIN : (

    Code:
    SELECT
      COUNT(*) AS count,
      SUM(total) - SUM(sales_tax) AS total,
      u.fname,
      u.note_color
    FROM
      users u
      LEFT JOIN orders o ON (u.id = o.sales_rep_id)
    WHERE
      o.date BETWEEN '2009-04-04' AND '2009-04-05' AND
      o.status_id NOT IN (5,6)
    GROUP BY
      u.id
    thanks,
    Leon

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,495 Times in 4,459 Posts
    Two errors:

    (1) Your GROUP BY is wrong. You must GROUP BY the fields that are *NOT* part of the aggregate expression.
    (2) Any time you use fields from the DEPENDENT table (the right table in a LEFT JOIN, left table in a RIGHT JOIN) in your WHERE clause, you *automatically* convert the outer join into an INNER JOIN!!!

    To see why, look at the third post in this thread:
    http://www.aspmessageboard.com/showthread.php?t=230397

    SO...

    Code:
    SELECT
      u.id,
      u.fname,
      u.note_color,
      COUNT(o.total) AS count,
      SUM(o.total) - SUM(o.sales_tax) AS total
    FROM
      users u
      LEFT JOIN orders o ON (
               u.id = o.sales_rep_id
           AND o.date BETWEEN '2009-04-04' AND '2009-04-05' 
           AND o.status_id NOT IN (5,6)
           )
    GROUP BY u.id, u.fname,  u.note_color
    I added u.id to the SELECT list because it occurred to me that you could have two u.fname values that are the same, with the same u.note_color values, too.

    MySQL may indeed be flexible enough to allow the GROUP BY on only u.id, but it's not ANSI SQL, so I'd strongly recommend going with the standard: Mention *all* non-aggregated fields in your GROUP BY.

  • Users who have thanked Old Pedant for this post:

    Leeoniya (04-05-2009)

  • #3
    Regular Coder
    Join Date
    Apr 2006
    Location
    Northbrook, IL
    Posts
    394
    Thanks
    8
    Thanked 6 Times in 6 Posts
    yep, that did it, thanks. i'm not sure it's necessary to group by anything other than u.id...it's unique already

  • #4
    Regular Coder
    Join Date
    Apr 2006
    Location
    Northbrook, IL
    Posts
    394
    Thanks
    8
    Thanked 6 Times in 6 Posts

    Question

    out of curiosity, is there any way to use a single query perhaps with a CASE to aggregate different date ranges in a single pass? and make month_count/month_total, ytd_count/ytd_total...etc columns. for each sales_rep_id as above

    thanks
    Leon

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,495 Times in 4,459 Posts
    First of all, as I said, MySQL might let you get away with just the GROUP BY u.id, but it's really a bad habit to get into as other DBs will not let you do it.

    As to the other...

    Sure, but you might find that in the long run you are better off doing that in your PHP/ASP/JSP code.

    But, just for an example:
    Code:
    SELECT
      u.id,
      u.fname,
      u.note_color,
      SUM( IF(MONTH(o.date)=3,1,0) ) AS marchCount,
      SUM( IF(MONTH(o.date)=3, o.total - o.sales_tax, 0 ) ) AS marchTotal
      SUM( IF(MONTH(o.date)=4,1,0) ) AS aprilCount,
      SUM( IF(MONTH(o.date)=4, o.total - o.sales_tax, 0 ) ) AS aprilTotal,
      COUNT( o.date ) AS ytdCount,
      SUM( o.total - o.salestax ) AS ytdTotal
    FROM
      users u
      LEFT JOIN orders o ON (
               u.id = o.sales_rep_id
           AND YEAR(o.date) = 2009 
           AND o.status_id NOT IN (5,6)
           )
    GROUP BY u.id, u.fname,  u.note_color
    You can, of course, use CASE WHEN in place of the IF, but for something like this the IF is smaller and easier to read.

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I really hate the fact MySQL allows columns in the SELECT clause that are not part of the GROUP BY clause. The results are random and too often I see people expecting one thing and not understanding why they're not getting what they expect.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,495 Times in 4,459 Posts
    Agreed. Why they decided to go non-ANSI on this simple aspect of queries is beyond me.


  •  

    Posting Permissions

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