Web Analytics Made Easy -
StatCounter need some left join help - CodingForum

Announcement

Collapse
No announcement yet.

need some left join help

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    "I only know that I know nothing."
    -Socrates

  • #2
    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.
    Be yourself. No one else is as qualified.

    Comment


    • #3
      yep, that did it, thanks. i'm not sure it's necessary to group by anything other than u.id...it's unique already
      "I only know that I know nothing."
      -Socrates

      Comment


      • #4
        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
        "I only know that I know nothing."
        -Socrates

        Comment


        • #5
          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.
          Be yourself. No one else is as qualified.

          Comment


          • #6
            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.

            Comment


            • #7
              Agreed. Why they decided to go non-ANSI on this simple aspect of queries is beyond me.
              Be yourself. No one else is as qualified.

              Comment

              Working...
              X