Sales volume report

Databases
1

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

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:

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

2

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

  FROM orders
... [i]additional joins[/i]
 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


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

:slight_smile:

3

Thanks but I have a Couple of Queries

Shouldn’t the code be:

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 
   [B][U]WHERE[/U][/B] 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:

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?

4

you botched the query when you turned my AND into WHERE

and you overlooked that i actually mentioned not to do that

:slight_smile:

5

Right sorry, I’m confused because as soon as I put the AND in the JOIN condition Access throws an error. JOIN not supported.

6

i used to know the workaround for this

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

7

Its parenthesis! Just like you said:

LEFT OUTER
  JOIN orders
    ON (
       orders.orderid = orderitems.orderid 
   AND orders.[date]> DATEADD("m",-1,DATE())
       )