SQL Query - get average dollar of an order withou using AVG()

I am gettig the average dollar value of an Order.   Just need to double check if this query is missing anything since  

there are a certain number of orders in the ORDERS table, but not all of these orders are in the ORDERITEMS table.  

SELECT  SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(ORDERS.ORDERID) AS 'Orders_Average'
 
  ,  SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(ORDERITEMS.orderid)  AS 'OrderItems_Average'
 
 
FROM  ORDERITEMS LEFT OUTER JOIN
               INVENTORY ON ORDERITEMS.PARTID = INVENTORY.PARTID RIGHT OUTER JOIN
               ORDERS ON ORDERITEMS.ORDERID = ORDERS.ORDERID
ocdcAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
gplanaConnect With a Mentor Commented:
Your query is getting two values. The first one is the average related to the total of orders. The second one is the average related to orders with items.

How can an order have no item? Is this because the order includes also some services?
If this is the case, I would just add an entry on item table with the price by hour of the service, so you can the just put the number of hours on the qty attribute, and this simplyfy your query a lot.

Hope it helps.
0
 
Chris LuttrellSenior Database ArchitectCommented:
What is your desired outcome?  Your query has no problems if you are wanting just the 2 numbers for total sales amount divided by total number of rows in the orders table and total number of rows in the orderitems table.  If you are expecting something else, describe it a little more.
0
All Courses

From novice to tech pro — start learning today.