ocdc
asked on
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*INVENTO RY.PRICE) / COUNT(ORDERS.ORDERID) AS 'Orders_Average'
, SUM(ORDERITEMS.qty*INVENTO RY.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
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*INVENTO
, SUM(ORDERITEMS.qty*INVENTO
FROM ORDERITEMS LEFT OUTER JOIN
INVENTORY ON ORDERITEMS.PARTID = INVENTORY.PARTID RIGHT OUTER JOIN
ORDERS ON ORDERITEMS.ORDERID = ORDERS.ORDERID
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.