SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sales volume report

    Sales Report

    Hi I'm trying to write a query that will produce a sales volume report for the last month for an e-commerce store. Its based around an Access Database. Table Structure follows.

    Products
    productid PK
    name

    Orders
    orderID PK
    date

    Orderitems
    id PK
    orderid FK
    productid FK
    quantity

    I have this

    Code:
    SELECT products.productid, Sum(orderitems.quantity) AS totalsold, products.[name] FROM orderitems, products, orders
    WHERE products.productid= orderitems.productid  AND orders.orderid=orderitems.orderid AND orders.[date]> DateAdd ("m", -1, date())
    GROUP BY products.[name], products.productid
    Which gives me the number of each item sold in the past month, but only for items where there have actually been sales. How would I adjust this to give me sales figures of 0 for products which haven't sold?

    I'm guessing its something like this:

    Code:
    SELECT products.productid, products.[name], IIF(Sum(orderitems.quantity)  IS NULL, 0, Sum(orderitems.quantity)) AS totalsold
    FROM products LEFT OUTER JOIN  orderitems ON orderitems.productid = products.productid INNER JOIN orders ON orders.orderid=orderitems.orderid
    WHERE  orders.[date]> DateAdd ("m", -1, date())
    GROUP BY products.[name], products.productid
    But I can't seem to get my nested joins right and the Access brackets around them.

    Can anyone help?

    Thanks
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    first thing i do when debugging a query is decide what the limiting factor is, the "variable" that determines the content of the report, as opposed to dumping ~everything~

    here, you want the last month's orders

    since this is a restriction on the orders table, and not on the product, i would ordinarily start with the orders table first in the FROM clause, since the optimizer will create an execution plan which retrieves only the fewest rows possible, and build joins from there, and the fewest rows would be only the orders whose date qualifies
    Code:
      FROM orders
    ... additional joins
     WHERE orders.[date] > DATEADD("m",-1,DATE())
    if orders has an index on [date], this query would be optimized already

    but unfortunately, you want products with zero sales as well, in other words, you want all products

    all products means that the products table has to be the "outer" table in an outer join

    Code:
    SELECT products.productid
         , products.[name] 
         , SUM(orderitems.quantity) AS totalsold
      FROM (
           products
    LEFT OUTER
      JOIN orderitems
        ON orderitems.productid  = products.productid
           )
    LEFT OUTER
      JOIN orders
        ON orders.orderid = orderitems.orderid 
       AND orders.[date]> DATEADD("m",-1,DATE())
    GROUP 
        BY products.[name]
         , products.productid
    several things to note:

    the parentheses are required by access (ptui), an additional level of parentheses for every additional table joined beyond the first

    once you go OUTER, you keep going OUTER -- the join from products to orderitems is a left outer join, so the join to orders has to be left outer as well

    notice the AND condition in the join to orders, this is where you restrict the orders to the last month, and ~not~ in the WHERE clause

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks but I have a Couple of Queries

    Shouldn't the code be:

    Code:
    SELECT products.productid
         , products.[name] 
         , SUM(orderitems.quantity) AS totalsold
      FROM (
           products
    LEFT OUTER
      JOIN orderitems
        ON orderitems.productid  = products.productid
           )
    LEFT OUTER
      JOIN orders
        ON orders.orderid = orderitems.orderid 
       WHERE orders.[date]> DATEADD("m",-1,DATE())
    GROUP 
        BY products.[name]
         , products.productid
    The double AND on the JOIN condition throws a JOIN NOT SUPPORTED Error in Access.

    And whilst that is nice and neat, it doesn't give me a zero count on products where there have been no sales.

    I tried changing the 3rd line to read:
    Code:
    SELECT products.productid
         , products.[name] 
        , IIF(SUM(orderitems.quantity) IS NULL, 0, SUM(orderitems.quantity)) AS totalsold
    But that didn't seem to do the trick. Have I missed something in your explanation?
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you botched the query when you turned my AND into WHERE

    and you overlooked that i actually mentioned not to do that

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you botched the query when you turned my AND into WHERE

    and you overlooked that i actually mentioned not to do that

    Right sorry, I'm confused because as soon as I put the AND in the JOIN condition Access throws an error. JOIN not supported.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i used to know the workaround for this
    Code:
    LEFT OUTER
      JOIN orders
        ON (
           orders.orderid = orderitems.orderid 
       AND orders.[date]> DATEADD("m",-1,DATE())
           )
    you first have to parenthesize (ptui) the join conditions, and then save the query, or open it in design view and then save it, or something...

    ... but it is fixable, and the AND is definitely what you want

    holler if you find a way to fix it
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its parenthesis! Just like you said:


    Code:
    LEFT OUTER
      JOIN orders
        ON (
           orders.orderid = orderitems.orderid 
       AND orders.[date]> DATEADD("m",-1,DATE())
           )
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com


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
  •