Click here to Skip to main content
12,506,283 members (54,889 online)
Rate this:
 
Please Sign up or sign in to vote.
How can I modify the query below to show "average dollar value for Orders" and "average dollar value for OrderItems" side by side in columns. The query below would combine them. Thank you.
SELECT (COUNT(Orders.orderid) / SUM(Inventory.price)) AS Orders_Average
FROM  ORDERITEMS LEFT OUTER JOIN
               Inventory ON OrderItems.partid = Inventory.partid RIGHT OUTER JOIN
               Orders ON OrderItems.orderid = Orders.orderid
               
WHERE NOT EXISTS 
(SELECT *   
FROM  ORDERITEMS
WHERE OrderItems.orderid <> Orders.orderid
GROUP BY ORDERITEMS.orderid)
Posted 18-Jan-13 10:20am
7prince414
Updated 18-Jan-13 16:34pm
v2
Comments
Tharaka MTR 18-Jan-13 22:38pm
   
Orders can have multiple order items. you are asking average value for orders, and average value for order items. could you please let us know how is your expecting output look likes
7prince 18-Jan-13 23:13pm
   
yes that is correct. I am asking average value for orders, and average value for order items.

I would like to get the output in this format:

two columns: "Orders Average" and "OrderItems Average") or in one column with each output in its own row. Thank you.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi

For average you need to do Sum over count and not count/sum.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160927.1 | Last Updated 20 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100