use Perl

• Guessing... (Score:1)

  by jarich (4909)

  I think your boss wants you to give a special to your customers who have given the business less than 15 pound ever. Yet you're grouping by order_date. Thus if your customer has purchased something for 10 pound last week and 10 pounds yesterday I would expect them to show up twice in the output:

  John, Smith, 2009-11-30, 10
  John, Smith, 2009-12-06, 10
  • Re: (Score:1)

    by link (3402)

    I think if you want some information about customers it is probably a bad idea not grouping by customer id. Depending on your site you may or may not have 2 customers with the same name but you will almost always have the occasional repeat customer.

    Actually , looking at the code I would assume I misunderstood the requirements as I don't see why order_date is there at all.
    • Re: (Score:2)

      by Ovid (2709)

      I should have removed order_date. It was present in the first version of the SQL I wrote.
• sum(null) is null, not zero (Score:1)

  by mscolly (9519)

  "price" I presume is from the order_items table. If there are no orders and hence no order_items, the "price" field is a null (from the outer joins). sum(null) is null, and null is not less than 15, the comparison is null. This means you will not see the customers with no (completed) orders.
  • Re: (Score:1)

    by mscolly (9519)

    Even worse, if a client has completed orders, and at least one incomplete order, this gives a null price, and the sum will be null again.
    • Re: (Score:2)

      by Ovid (2709)

      What do you mean by "incomplete order"? I just added an order for a customer who already had orders, but did not add order items for it. The query results were the same, so I think I may have misunderstood you.
      • Re: (Score:1)

        by mscolly (9519)

        An "orders" record without corresponding "order_items" record(s) would be an "incomplete" order.

        Regarding your example, the result depends on whether there were other "complete" orders for the customer or not, and how your database treats nulls. Since a null means "totally unknown", anything plus null results in null, and comparing null with 15 is also null (neither true nor false). You should be using sum(coalesce(price,0)) for your definition of the field total.
• try fixing your query (Score:1)

  by jsievert (9521)

  SELECT first_name,
                      last_name,
                      order_date,
                      SUM(COALESCE(price, 0)) AS total
  FROM customer
  LEFT JOIN orders ON customer.id = orders.customer_id
  LEFT JOIN order_item ON orders.id = order_item.order_id
  GROUP BY first_name, last_name, order_date
  HAVING total 15
  ORDER BY order_date ASC

  there
• group by customer ID (Score:1)

  by kbo (9327)

  I believe you should be grouping by customer_id rather than first/last name... otherwise you could be combining results for two John Smiths that order on the same date.
  • Re: (Score:2)

    by Ovid (2709)

    Ouch. Two bugs. Thanks :)